Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting rows into a list
I'm hoping somebody can help with this! I've searched and searched
past messages and can't find anything to work. I have a list of over 500 accounts. Inbetween each account I need to paste in five lines (contained in 2:6 in the same sheet). The list starts in A10. The cells that are being inserted contain formulas, and need to stay formulas. The number of accounts in the list will vary from month to month. I'm thinking maybe I'm having a blonde moment and there's a very easy answer to this..... Thank you in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting rows into a list
This will copy everything in rows 2:6 and paste between every row
beginning on row 11. It will also paste after the last account. Of course, you realize that this will only work right the first time. Thereafter, you will have to insert as you add a new account, or get a new macro that will accomodate the adding of an account. Sub insertFmlas() lastRow = Cells(Rows.Count, 1).End(xlUp).Row Range("$A$2:$IV$6").Copy Cells(lastRow + 1, 1).PasteSpecial , Paste:=xlAll For i = lastRow To 11 Step -1 If Not Cells(i, 1) Is Nothing Then Range("$A$2:$IV$6").Copy Cells(i, 1).EntireRow.Insert End If Next CutCopyMode = False End Sub " wrote: I'm hoping somebody can help with this! I've searched and searched past messages and can't find anything to work. I have a list of over 500 accounts. Inbetween each account I need to paste in five lines (contained in 2:6 in the same sheet). The list starts in A10. The cells that are being inserted contain formulas, and need to stay formulas. The number of accounts in the list will vary from month to month. I'm thinking maybe I'm having a blonde moment and there's a very easy answer to this..... Thank you in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting rows into a list
P.S. You need to make the sheet with your list the active sheet when you run
the macro. I did not know the sheet name or number, so I did not specify which one in the macro. " wrote: I'm hoping somebody can help with this! I've searched and searched past messages and can't find anything to work. I have a list of over 500 accounts. Inbetween each account I need to paste in five lines (contained in 2:6 in the same sheet). The list starts in A10. The cells that are being inserted contain formulas, and need to stay formulas. The number of accounts in the list will vary from month to month. I'm thinking maybe I'm having a blonde moment and there's a very easy answer to this..... Thank you in advance! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting rows into a list
Use this one:
Sub insertFmlas() lastRow = Cells(Rows.Count, 1).End(xlUp).Row Range("$A$2:$IV$6").Copy Cells(lastRow + 1, 1).PasteSpecial , Paste:=xlAll For i = lastRow To 11 Step -1 If Not Cells(i, 1) Is Nothing Then Range("$A$2:$IV$6").Copy Cells(i, 1).EntireRow.Insert End If Next Application.CutCopyMode = False End Sub I always forget to put Application on there for VBA. " wrote: I'm hoping somebody can help with this! I've searched and searched past messages and can't find anything to work. I have a list of over 500 accounts. Inbetween each account I need to paste in five lines (contained in 2:6 in the same sheet). The list starts in A10. The cells that are being inserted contain formulas, and need to stay formulas. The number of accounts in the list will vary from month to month. I'm thinking maybe I'm having a blonde moment and there's a very easy answer to this..... Thank you in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting filtered rows | Excel Discussion (Misc queries) | |||
pasting into a list using a macro | Excel Programming | |||
Pasting rows below the last row with data? | Excel Programming | |||
Pasting in Rows? | Excel Discussion (Misc queries) | |||
Pasting formulas to different rows | Excel Worksheet Functions |