VBA Copy Row and Paste as Formulas "X" number of times

[activeman]

I have been kindly supplied with the following code that allows the Document User to define the number of rows to be inserted in a Worksheet.

Currently this code copies row 3 and pastes it the defined number of times beneath the last used row.

However I would like to tweak the code so that rather than pasting all of row 3 into the Worksheet I would like it to only paste the formulas from row 3.

Unfortunately my VBA is no where near good enough to work this one out so would anyone else have any ideas?

Sub InsertRows()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Dim lngRows As Long, lngNextRow As Long<o></o>
<o> </o>
On Error GoTo Finish<o></o>
<o> </o>
lngRows = CLng(InputBox("How many rows do you wish to insert?"))<o></o>
lngNextRow = Range("A" & Rows.Count).End(xlUp).Row + 1<o></o>
<o></o>
Rows(3).Copy Rows(lngNextRow & ":" & lngNextRow + lngRows - 1)<o></o>
<o></o>
Finish:<o></o>
If Err.Number <> 0 Then MsgBox Prompt:="Please ensure you only enter numeric values!"<o></o>
<o> </o>
End Sub<o></o>

 

[wigi]

Add 1 row:

Sub InsertRows()

Dim lngRows As Long, lngNextRow As Long

On Error GoTo Finish

lngRows = CLng(InputBox("How many rows do you wish to insert?"))
lngNextRow = Range("A" & Rows.Count).End(xlUp).Row + 1

Rows(3).Copy Rows(lngNextRow & ":" & lngNextRow + lngRows - 1)
Rows(lngNextRow & ":" & lngNextRow + lngRows - 1).SpecialCells(2).ClearContents

Finish:
If Err.Number <> 0 Then MsgBox Prompt:="Please ensure you only enter numeric values!"

End Sub

That line will delete all cells, in the chosen rows, containing fixed constants. So the formulas remain.

Please use code tags when you paste code on the forum here. It makes code much more readable.

Wigi

 

[activeman]

Brilliant - many thank you's