Insert "X" number of Rows Based on User Input

[activeman]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Using Excel VBA I am looking to automate a basic Copy and Paste routine which amounts to little more than copying row 3 and then pasting it in the next blank row below.<o></o>
<o> </o>
However I want the User to decide on the “number” of rows they want pasting into the Worksheet based on an input box.<o></o>
<o> </o>
Based on this “number” the macro will copy row 3 find the next blank row and copy row 3 the applicable “number” of times as determined by the User.<o></o>
<o> </o>
E.g. <o></o>
Run Macro<o></o>
Input Box asks User how many rows they would like pasting?<o></o>
User Inputs “50”<o></o>
Macro copies Row 3 and then pastes row 3 into the next blank row 50 times over<o></o>
<o> </o>
Any help would be much appreciated?<o></o>

 

[Mike Blackman]

Hi,

Maybe something like this?

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)
       
Finish:
    If Err.Number <> 0 Then MsgBox Prompt:="Please ensure you only enter numeric values!"

End Sub

 

[activeman]

Excellant Sorted - thanks Mikey B

 

[activeman]

Hi,

Maybe something like this?

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)
 
Finish:
    If Err.Number <> 0 Then MsgBox Prompt:="Please ensure you only enter numeric values!"
 
End Sub

I am attempting to adjust the above code so rather than copying and pasting row 3 it copies and pastes the FORMULA's ONLY from row 3.

Not getting too far would anyone have any ideas how to do this?

 

[ECUPIRATE]

I am attempting to adjust the above code so rather than copying and pasting row 3 it copies and pastes the FORMULA's ONLY from row 3.

Not getting too far would anyone have any ideas how to do this?

Same.