looking for empty row to paste a range of copied cells

Hi - I am trying to make a code that will copy a range ("A27:L27") on sheet 1 
and then look for the next empty row on sheet 2 and paste it in range 
("A27:L27") .  I would also like the macro to insert a new blank row (or 
insert the copied row) for the purpose of shifting existing SUM functions on 
sheet 2 down.  I would like those functions to be right below the 
copied/pasted cells every time the macro is executed.

Thanks for any help - Jim A
0
JimA (42)
2/12/2009 12:22:01 AM
excel 39880 articles. 2 followers. Follow

1 Replies
674 Views

Similar Articles

[PageSpeed] 16

You don't Mention What column you want to sum

This code will copy and paste to the fist row and then sum column D

Sub CopyPasteSum()
    Dim r As Range
    Dim s As Range

    Set s = Range("D65536").End(xlUp)

    s.ClearContents

    Worksheets("Sheet1").Range("A27:L27").Copy Destination:=Worksheets
("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)

    Set r = Range("D1", Range("D65536").End(xlUp))

    Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(2, 3) =
WorksheetFunction.Sum(r)

End Sub
0
davesexcel (61)
2/12/2009 1:57:02 AM
Reply:

Similar Artilces:

All my icons look the same (except IE8 and the recycle bin)
i cant open any windows...when i click on them the open with window pops up and wants me to pick wat i want to open it with...i can only get on the internet in safe mode with networking. and when i try to open...for example paint it says invalid bitmap, or its format is not currently supported and other windows wont even open. got any ideas on how i can fix this?? On Sun, 30 May 2010 06:54:01 -0700, xxkoreanxx wrote in message <news:999F2CE9-8E21-47DC-8BA4-BAC1F1329F6E@microsoft.com>: > i cant open any windows...when i click on them the open with window pops up > a...

option to insert column and row breaks
There should have an option to insert column breaks for printing. For example, when printing a sheet with only one column, but with 200 rows, it could be automatically broken into 4 columns of 50 rows, all fitting in just one page, instead of 4. This would save paper and allow easy reading of this kind of sheets. The same logic could be applied to rows... Thanks. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see ...

Losing cell borders when I copy and paste
When I copy and paste into a blank cell my borders from the cell dissapear. What am I doing wrong? Are you copying a bordered cell onto an un-bordered cell? Format from source will go with the source. If copying an unbordered cell onto a blank bordered cell, source format will wipe the target cell format. Gord Dibben MS Excel MVP On Sat, 19 Apr 2008 15:47:00 -0700, Davo <Davo@discussions.microsoft.com> wrote: >When I copy and paste into a blank cell my borders from the cell dissapear. >What am I doing wrong? Try paste special -> Values. -- HTH, Barb Reinhardt &qu...

looping through columns and rows
Hello, Can someone show me the code which would allow me to search through a number of rows and columns, to identify a criteria. Each time the criteria is found, 1 is added to a variable. When the variable reaches a set number, the search moves to a different column. This would allow me to allocate people to jobs according to their first, second, third choice etc, without resourcing too many people to a job. can you help? I have very little experience, but am usually able to see what is happening in the code and expend on it to take care of more elements. kind regards, Matt Check one...

Looking for feedback
Hey everyone just started my own business doing consulting / training for Microsoft Outlook please check out my site www.outlookchallenged.com and provide any suggestions / feedback please. Any advice is good advice at this point. Thanks -- www.outlookchallenged.com Br Sense <outlookhelp@ftard.com> wrote: > Hey everyone just started my own business doing consulting / training > for Microsoft Outlook please check out my site > www.outlookchallenged.com and provide any suggestions / feedback > please. Any advice is good advice at this point. The best advice I have to offer ...

Copying Formulas to rows
Hi All, I have created a book which has various formulas accross a row which are all worked out from figures in that row. But I cant figure how to make the whole sheet use these forumlas, i.e. when i start typing info on row 2 it uses the same formulas as on row 1. The only way I have worked out so far to do this is to copy and paste each row, but this is time consuming and im sure there must be a quicker way? Thanks in advance for your help. -- mdj101 ------------------------------------------------------------------------ mdj101's Profile: http://www.excelforum.com/member.php?a...

Linking contents of multiple cells to an external object
Hope I'm posting to the right forum. If not could someone please tell me where the appropriate place is. I've coded in Excel a register for a small college that allows the user to enter whether a student attended a particular subject. Each worksheet shows the whole class for a month, with students as rows & dates as columns & the subject appears in the cell. Totals appear on a seperate sheet. Where a student is absent there are a few alternative codes & on a seperate line room for a short comment. What is required is to automatically pull out the abscences on a separa...

Deleting multiple rows in Datagrid
Hi Everyone, I am using Datagrid with Checkbox and other data fields. In addition to the datagrid i have a button labelled 'Delete'. When i click on the 'Delete' button, all the checked rows must be deleted from the database. Can anyone guide me by giving sample code or any help in this regard will be appriciated. Regards, Nazar. ...

Pasting without updating cell references in formulas
I am a relatively low end user of Excel. I need to copy a large section of formulas to create a new section of my spreadsheet. Excel does an excellent job of changing all cell reference in the formulas based on the number of cells that you cover in your copy and paste. HOWEVER, in this case I want almost all cell references to remain the same. I can then edit each one and change the one that I want changed. Is there any easy way to do this. I'm sure there is. Thanks in advance Reg As long as you are moving to the same sheet, just highlight the cells you want to move and using mou...

Transfering numbers into a row
Hi is there a way to move or copy a column of numbers in cells into a row of cells? thanks Daniel Copy the column, put cursor where you want it to go, select Paste-Special, Transpose Excel XP. -- Hans Manhave Jack "Daniel" <nospam@nowhere> wrote in message news:OrlhKbEGFHA.1408@TK2MSFTNGP10.phx.gbl... > Hi > > is there a way to move or copy a column of > numbers in cells into a row of cells? > > thanks > > Daniel > Excellent, thanks for your help regards Daniel "HansM" <hmanhave@jackson-lloyd.com> wrote in message new...

Automatically change row height?
Hello. The user types text stuff in A1. If there is no text, the row height for A2 is zero/minimal. If there is text, A2's row height autofits to that text. Some users enter more text than others, so A2's height has to grow or shrink depending on what is in A1. The formula in A2 is IF (A1="","",A1&", "&F16) Is this possible without VBA? If not, does anyone have any good code for this? Thanks! VR/Lost Private Sub worksheet_change(ByVal target As Range) Set target =3D Rows("2:2") If Range("A1").Value <> "" Th...

Looking for CRM Solution
Does anyone use, or has anyone encountered, a CRM based solution used for residentail, multifamily property management? On Apr 28, 12:55=A0pm, MrB <M...@discussions.microsoft.com> wrote: > Does anyone use, or has anyone encountered, a CRM based solution used for > residentail, multifamily property management? The beauty of Microsoft CRM is that it can be customized for any business in any industry. It's ability to be customized using front- end tools (not within code) is , as far as I know, unparalleled by any other software package. You have the ability to create new entiti...

=COUNTA() with multiple look-up
Hi All, I would like to count the number of items that have a value "AAA" in column A and an amount >0 in column B. As I am not quite sure how to incorporate two look-ups in a counta formula I was wondering if anyone can give me the formula? Many thanks! Rgds, Robert Try something like this: =SUMPRODUCT((A1:A100="AAA")*(B1:B100>0)) or....if there may be some text cells in B1:B100 interspersed with the numbers =SUMPRODUCT((A1:A100="AAA")*ISNUMBER(B1:B100)*(B1:B100>0)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP &...

move rows from column to column
I have Column b with Name in one row and number in 2nd row want to move all numbers to Column a and leave name in Column b without having to move each one seperate. 1500 records. Use a help column, in the first adjacent cell type =ISNUMBER(A2) where A2 is the first cell with name copy down 1500 rows by either dragging the formula or double click the lower right corner of the help cell (as long as it is in an adjacent column) Then apply data>filter>autofilter and filter on FALSE in the help columns, select all visible cells and copy and paste somewhere else, then select TRUE from ...

can I edit a picture to look faded?
I want to edit my picture to be faded so I can use it as background with text over it. "deborahm" <deborahm@discussions.microsoft.com> wrote in message news:4D93DD63-8553-447D-A0AC-44EE0E1BD9B3@microsoft.com... > I want to edit my picture to be faded so I can use it as background with > text > over it. In Publisher you can do it one of two ways. For both ways start by inserting the picture. Method 1, right click on picture and select Format Picture, click on picture tab, then click on the dropdown for Color under Image Control and select wash...

Excel Ranges and Areas Problem
We need to set the range from text sent to us. Below is an exmple. Can anyone help as the issue is that normally the range the example below only references cells [C4, B2, D8] in a sheet and not all the sheets and cells [Sheet1!C4, Sheet2!B2, Sheet3!D8]. Sub test() On Error GoTo iErrors Dim strText As String strText = "Sheet1!C4, Sheet2!B2, Sheet3!D8" Dim rngTest As Excel.range Set rngTest = Application.range("Sheet1!C4, Sheet2!B2, Sheet3!D8") Debug.Print rngTest.Address Exit Sub iErrors: Err.Clear End Sub 'We need the rngTest.Ad...

Need invisible/shaded/highlighted cell
I'm thinking what I would like to do can *probably* be done in VBA, but I'd like to use the formula in the cell. If it can be done. I have three columns: DOW DATE BILL DTE --- -------- -------- Wed 02/01/06 02/03/06 Thu 02/02/06 02/06/06 Fri 02/03/06 02/07/06 Sat 02/04/06 02/06/06 Sun 02/05/06 02/07/06 Mon 02/06/06 02/08/06 Tue 02/07/06 02/09/06 Wed 02/08/06 02/10/06 . . . . The date col contains a formula to add 1 to the cell above. The day of week col uses the date from the date column. And the bill date col adds 4 days to the date col if it falls on a "Thu...

empty ranges
hi all, what would be the vb code to chech a range to see if anything (data) was there. say range j12:x12. thank you in advance S1 Maybe If Application.CountA(Range("J12:X12"))> 0 Then ... -- HTH RP (remove nothere from the email address if mailing direct) "S1" <anonymous@discussions.microsoft.com> wrote in message news:18ec01c504ad$ca4c0480$a601280a@phx.gbl... > hi all, > what would be the vb code to chech a range to see if > anything (data) was there. say range j12:x12. > > thank you in advance > S1 thank you Bob S1 >...

data look up and return values across a row
I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need to cross reference with another larger spreadsheet, which we'll call 'spreadsheet B'. I'd like to remove everything from B that isn't on A. Is there an easy way of doing that? Chip Pearson has lots of info about working with duplicates at: http://www.cpearson.com/excel/duplicat.htm Betsey wrote: > > I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need > to cross reference with another larger spreadsheet, which we'll call > &#...

Shading Rows and/or Columns in an Excel Table
I created a financial table with interest rates on the first row an time periods on the first column. For each combination of interest rat and time period, I've computed a future value factor. What I'd like t do is this - when I click on a certain interest rate, the column for i will change to a different color, and when I click on a certain period the row for it will change to the same color. It will then be easier t locate the future value factor - one that lies in the intersection o the highlighted row and column. Thanks in advance for your help -- Nerdzo ------------------------...

Linking a cell to update when rows added
HELP! How can I link row that will automatically move up / down if something is added to the linked row? example - I put notes next to client accounts, but if an updated client list adds a row, i want the notes to still be linked to the original cell Thanks in advance ...

copying and/or bookmarking a chart?
From: "me" <someone@microsoft.com> Subject: Re: bookmark a chart? Date: Friday, August 26, 2005 2:59 PM Thank you, this was helpful, but doesn't quite solve my problem which I should have stated earlier. BookA has the hyperlink to a chart in BookB. I need to copy the chart from BookB into a new worksheet in BookA using VBA. How would I do that? Thanks again, Mike "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message news:430F80A4.1060707@contexturesXSPAM.com... > You can't hyperlink to the chart sheet, but Jon Peltier has a macro > worka...

Change font color for row
If A1 is numbered 1 thru 4, representing Black, blue, green and red, is there a way to change the font color for an entire row based on this? i.e. if I place a 2 in cell A1, I would like the entire row of numbers and statements to change to a blue font. Thanks in Advance Mac "Mac" <Mac@discussions.microsoft.com> wrote in message news:12170EBC-BF99-4759-9171-C8B319364690@microsoft.com... > If A1 is numbered 1 thru 4, representing Black, blue, green and red, is > there > a way to change the font color for an entire row based on this? i.e. if I > place a ...

How do I use the "Correlation" dialog box with multiple ranges?
I'm using data in an Excel 2003 in a workbook, trying to see if there is any correlation between specific offices in which people work and the length of time they have been employed, their age the amount of sickleave taken and/or the amount of sickleave balance they have. I'm attempting to use Data Analysis and the Correlation dialog box. Although the dialog description tells me that I can use multiple comparison ranges, I don't see how to do it. Thanks for any help. Paulf6 - Arrange your data in list (database) format, i.e., names in the top row (Time Employed, Age, ...)...

Format cell to display numbers instaed of ###'s
Not sure how to querry this in the KB.... but I want to be able to have a number such as 4 1/8 in a cell that is set at width less than what is required to display it properly. Same with a date/time entry. In cells where I'm entering text, the text will "spill" over if the adjoining cells are empty of data. No so with number's. How do I change it so it will display as 4 1/8 without widening the cell? If I place an apostrophe ' in front of the number, or format the cell as plain text it will work, but I really need to retain the numeric value. Thanks, David... ...