Copy/insert a row to a position defined in a form

**bob_x28** · 08-12-2009, 07:38 AM

Hi, I'm not an experienced VBA programmer, so hopefully (and probably) for you it's easy to tell me how to do it:

So far, I copy a row(1) to another position (7) with this macro:

Rows("1:1").Select
Selection.Copy
Range("A7:BU7").Select
Selection.Insert Shift:=xlDown
Range("A7:BU7").Select
Application.CutCopyMode = False
Range("A7").Select

That works well so far.
Now what I want to do is, to use a form and enter a number and have the row 1 inserted in this row number (because it should not always be in row 7). For example that i can insert 38 in the form and it will copy the row number 1 to row number 38.

I tried this
Range("frm_insertrow.txt_rownumber").Select

Which you're probably laughing at now

I hope somebody can help me, thanks!

**Palmetto** · 08-12-2009, 08:20 AM

The code below will copy only row-1 to which ever row number is entered into the input box.

It could be made more flexible to allow the user to specify which row to copy by using another input box - but you did not ask for this.

Sub Insert_Row()

    Dim lRow As Long
    
    On Error Resume Next
    lRow = Application.InputBox _
        (Prompt:="Enter the row number at which to insert the data", _
            Title:="Enter a Number", _
            Type:=1)
            
    On Error GoTo 0

    Application.DisplayAlerts = True
    
    If lRow = 0 Then Exit Sub
    
    Range("1:1").Copy
    
    Cells(lRow, 1).EntireRow.Insert shift:=xlShiftDown
    
    Application.CutCopyMode = False

End Sub