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!

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!

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!

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!