subscript out of range to copy to specific sheet

Ali

In the attached revised file, I've rewritten your code (and added explanatory comments). For convenience, I added a "Launch Form" button on worksheet "SH" with this code:

Sub TextBox1_Click()
    UserForm1.Show
End Sub

Firstly at the top level of the Userform code page (so it's "remembered"), I've declared a variable Opt to capture the user's button choice:

Dim Opt As String

Then each Optionbutton has code like this:

Private Sub OptionButton1_Click()
    ' overwrite the variable
    Opt = Me.OptionButton1.Caption
    'set button colour to light green
    Me.CommandButton1.BackColor = 12648384
End Sub

(but the bold numbers change). E.g. if the button labelled "SH" is clicked, Opt will become "SH" and the CommandButton will turn light green.

When the green commandbutton (now "Add Name...") is clicked, this code runs:

Private Sub CommandButton1_Click()
    'do nothing unless a button was clicked
    If Opt = "" Then Exit Sub
    ' with the chosen button (sheet):
    With ThisWorkbook.Worksheets(Opt)
        ' find extent (last row) of column A
        LstRw = .Cells(Rows.Count, 1).End(xlUp).Row
        ' loop down rows
        For n = 2 To LstRw
            ' check if column B is empty then add text
            If IsEmpty(.Cells(n, 2)) Then .Cells(n, 2).Value = Me.TextBox1.Text
            ' check if column C is empty then add text
            If IsEmpty(.Cells(n, 3)) Then .Cells(n, 3).Value = Me.TextBox2.Text
        Next n
    End With

    ' reset variable and button to grey
    Opt = ""
    Me.CommandButton1.BackColor = -2147483633
    'Close form
    Me.Hide
    Unload Me

End Sub

Note that it relies on your labels matching the worksheet names (if not, an error will occur); also that it will fill empty cells separately (e.g. if B is filled but C empty, it will write the value to C). If that is to be avoided, you could write both by replacing that portion with:

For n = 2 To LstRw
            ' check if column B is empty then add text to B and C
            If IsEmpty(.Cells(n, 2)) Then 
                 .Cells(n, 2).Value = Me.TextBox1.Text
                 .Cells(n, 3).Value = Me.TextBox2.Text
            End if 
      Next n

Hope this makes sense and works for you. If so, please remember to mark this Answer as Selected.

Hello Ali,

The "subscript out of range" error is telling you that you are trying to reference something or a variable that does not exist in your code. Based on this, review that line and figure out what is being reference that has not been declared.

Cheers   :-)