Hey there,

I have a spreadsheet with 3 sheets. I need some help of how to insert a new blank row where a user may specify he/she wants it i.e where this new row goes no one knows!!!!!


The problem gets trickier as the three sheets are completely identical, just holding different bits of information on price, quantity and revenue. So if a new row goes into the price sheet - a similar one must go into the other two spreadsheets.

I managed to get the following code which uses an input box - But i would like to use an REFEDIT Control (which i have little experience in using).

TheRow = InputBox("Enter the line below where you want the blank row to be inserted")
If IsNumeric(TheRow) Then
    For n = 1 To 3
        Sheets(n).Rows(CInt(TheRow)).EntireRow.Insert
    Next n
Else
    MsgBox "You must enter a number"
End If

Also I require an extension to the code - whereby the user can specify what he wants in that new i.e what he wants to put in there and this need to be transferred to all the sheets.


Any direction/help much appreciated!!!!!

Why not have the user pick on screen for the row? Make your code like this:

Sub InsertRow()
    Dim TheRow As Range
    On Error GoTo InsertRow_ErrorHandler:

    'This gets the user to pick
    Set TheRow = Application.InputBox(Prompt:="Enter the line below where you want the blank row to be inserted", Type:=8)
    If Not TheRow Is Nothing Then
        'Allow only one row
        For n = 1 To 3
            Sheets(n).Rows(TheRow.Row).EntireRow.Insert
        Next n
    Else
        MsgBox "You must enter a number"
    End If

InsertRow_ErrorHandler:
    Select Case Err.Number
        Case 0
        Case 424
            Err.Clear
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure InsertRow of Module Module1"
            Err.Clear
    End Select
    'Clean Up
    Set TheRow = Nothing
End Sub


Dave