Adapt variable column macro for other worksheets

[JONPM]

I have this Macro for alphabetically sorting a cells in column AB

I would like the macro to work with other worksheets however the other worksheets in the workbook have different titles to BRIM.

What would I change in this macro so that it would work with other worksheets??

Many thanks in advance


' PWRII Macro''LR = Worksheets("BRIM").Range("AB" & Rows.Count).End(xlUp).RowActiveWorkbook.Worksheets("BRIM").Sort.SortFields.ClearActiveWorkbook.Worksheets("BRIM").Sort.SortFields.Add Key:=Range("AB2:AB" & LR) _, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormalWith ActiveWorkbook.Worksheets("BRIM").Sort .SetRange Range("A1:AB" & LR) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .ApplyEnd WithEnd Sub</pre>

 

[JLGWhiz]

Everywhere you see "ActiveWorkbook.Worksheets("BRIM")" in the code, just replace it With "ActiveSheet" (without the quotemarks). Then make sure the sheet you want to run the code on is the activesheet by selecting it and then run the macro.

 

[ZAX]

Everywhere you see "ActiveWorkbook.Worksheets("BRIM")" in the code, just replace it With "ActiveSheet" (without the quotemarks). Then make sure the sheet you want to run the code on is the activesheet by selecting it and then run the macro.

Here's An Easier Way To do This Because It Will Apply It To All Sheets Automatically:

Sub PWRII()
For SCount = 1 To Sheets.Count
    LR = Worksheets(SCount).Range("AB" & Rows.Count).End(xlUp).Row
    ActiveWorkbook.Worksheets(SCount).Sort.SortFields.ClearActiveWorkbook.Worksheets(SCount).Sort.SortFields.Add Key:=Range("AB2:AB" & LR) _
    , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(SCount).Sort
        .SetRange Range("A1:AB" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next
End Sub

ZAX

 

[JONPM]

Thanks I used the ActiveSheet method — great instructions it's meaning that through trial, error and simply doing I'm beginning to understand how macros work and how I can write them.

Thanks J