VBA Excel: Using an If statement to define an object

Question

I have combined two workbooks into one and would like the macro to work for each worksheet. (I'm fine with having to add to the code each year for it to continue to work, not a problem.) I keep getting a debug code - can you help me?

Sub CustomSort()

Dim tablename As String
If ActiveWorkbook.activeworksheet = ActiveWorkbook.activeworksheet("2021 Data") Then
tablename = Table1
ElseIf ActiveWorkbook.activeworksheet = ActiveWorkbook.activeworksheet("2020 Data") Then
tablename = Table115
Else
MsgBox ("You can't do that")
End If

Range("B2").Select
    Selection.AutoFilter
    Selection.AutoFilter
    ActiveWorkbook.activeworksheet.ListObjects(tablename).Sort.SortFields.Clear
    ActiveWorkbook.activeworksheet.ListObjects(tablename).Sort.SortFields.Add2 _
        Key:=Range("[Last name]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.activeworksheet.ListObjects(tablename).Sort.SortFields.Add2 _
        Key:=Range("[Month ID]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.activeworksheet.ListObjects(tablename).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Is there only one table on the worksheet? If so, you could just use `.ListObjects(1)` and skip checking the worksheet name. — BigBen, Jul 06 '21 at 19:56
A `Workbook` does not have an `ActiveWorksheet` property btw. — BigBen, Jul 06 '21 at 19:57
You will also benefit from [avoiding Select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Jul 06 '21 at 21:56

VBA Excel: Using an If statement to define an object

0 Answers0