Have User Form populate only the active sheet not by sheet name.

[Tmoske]

I have a userform in my workbook with a button to access it on twenty different sheets in the workbook. Currently the userform will only populate the sheet titled "Blank1". How can I change it so it will populate the active sheet without naming it? So no matter what sheet I'm on when I hit the submit button the userform will populate only the sheet I selected the button on? See macro below. Thanks!

Private Sub OKButton_Click()
    Dim NextRow As Long
    Sheets("Blank1").Activate
    
'   Unprotect Sheet
    ActiveSheet.Unprotect
    
' Sort Macro
    ActiveWorkbook.Worksheets("Blank1").ListObjects("Table1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Blank1").ListObjects("Table1").Sort.SortFields.Add _
        Key:=Range("D5:D55"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Blank1").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'   Protect Sheet
    ActiveSheet.Protect
    
'   Close Userform
    Unload Me
End Sub

 

[JLGWhiz]

I have a userform in my workbook with a button to access it on twenty different sheets in the workbook. Currently the userform will only populate the sheet titled "Blank1". How can I change it so it will populate the active sheet without naming it? So no matter what sheet I'm on when I hit the submit button the userform will populate only the sheet I selected the button on? See macro below. Thanks!

Private Sub OKButton_Click()
    Dim NextRow As Long
    Sheets("Blank1").Activate
    
'   Unprotect Sheet
    ActiveSheet.Unprotect
    
' Sort Macro
    ActiveWorkbook.Worksheets("Blank1").ListObjects("Table1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Blank1").ListObjects("Table1").Sort.SortFields.Add _
        Key:=Range("D5:D55"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Blank1").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'   Protect Sheet
    ActiveSheet.Protect
    
'   Close Userform
    Unload Me
End Sub

Delete this line of code:

Code:
Sheets("Blank1").Activate
Code:

Change this:

ActiveWorkbook.Worksheets("Blank1")

To this:

ActiveSheet

In both places. Actually, every place that the first statement appears.

 

[Tmoske]

Thanks! that worked fine but now I have an issue with the sorting part of the macro. I get the error "Subscript out of range"
It refers to Table1, but on each of my sheets the Table name is different. Is there a way around this?

ActiveSheet.ListObjects("Table1").Sort.SortFields.Clear
    ActiveSheet.ListObjects("Table1").Sort.SortFields.Add