How to refer to dynamic sheet in Excel VBA instead of Sheet Name - Instead of "Sheet16", i want to refer to the ActiveSheet, please see below

Sub Macro1()

Cells.Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet16").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet16").AutoFilter.Sort.SortFields.Add2 Key:= _
    Range("M1:M12"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
    :=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet16").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

CodePudding user response：

Just need to change it to (ActiveSheet.Name)

CodePudding user response：

The usage of ActiveSheet (as well of that of ActiveCell, Selection and the like of) should be avoided in favor of that of variables of proper class (e.g.: Worksheet, Range) as follows:

Sub Macro1()

    ActiveSheet.UsedRange.Copy ' handle only relevant cells
    Sheets.Add After:=ActiveSheet
    
    Dim newSh As Worksheet
        Set newSh = ActiveSheet ' set the currently active sheet to the 'newSh' variable of 'Worksheet' class
        
        With newSh ' reference 'newSh' variable
        
            .Paste
            
            Application.CutCopyMode = False
            
            .UsedRange.AutoFilter
        
            Dim keyRng As Range
                Set keyRng = .Range("M1:M12") ' set M1:M12 cells range of the referenced sheet as the one for sort keys
                
                With .AutoFilter.Sort ' reference 'Sort' property of 'Autofilter' object of referenced sheet
                    With .SortFields ' reference 'SortFields' object of referenced 'Sort' property
                        .Clear
                        .Add2 Key:=keyRng, _
                                   SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
                    End With
                    
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With

        End With

End Sub