dynamic sheetnames in macro

[kendel]

Hello, I want to be able to run this sort on any sheet in the workbook. I used the recorder to get this but don't know how to make it dynamic, it only works on sheet m2b (2). What do I change '.Worksheets("m2b (2)")' to? Is there a way to not refer to the sheet at all?

ActiveWorkbook.Worksheets("m2b (2)").sort.SortFields.Clear
ActiveWorkbook.Worksheets("m2b (2)").sort.SortFields.Add Key:=ActiveCell. _
Range("A1:D1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("m2b (2)").sort
.SetRange ActiveCell.Range("A1:D1")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply



Thanks,
Kendel, Excel 2007 & 2010

 

[AlphaFrog]

Use the keyword ActiveSheet to reference the currently active sheet.

    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ActiveCell. _
                        Range("A1:D1"), SortOn:=xlSortOnValues, _
                        Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange ActiveCell.Range("A1:D1")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply

 

[kendel]

Thank you very much AlphaFrog, that works just how I need it to.
Kendel