"Generic" Autofilter Macros Applied to Different Worksheets

[ACF0303]

Greetings,

I have a workbook with multiple worksheets and for each worksheet I want to be able to perform the same functions (when I am on that worksheet). The macros are pretty simple: I want to be able to sort from high to low, low to high, a to z and z to a for a selection of columns. Every worksheet will have the same column headers, although not the same number of rows of data.

My question: how can I create the macros so that I do not have to write the same ones over and over for each different sheet? Is there a way to write ones that specify the active worksheet and can adjust the range to include any rows with data in them?

Thanks for any help!

Cathy

 

[VoG]

Try recording a macro whilst doing this manually and post the recorded code in your reply.

 

[ACF0303]

Sure. Here is what it looks like. Now imagine - I have a number of other worksheets that I want to apply the same macro to, but they are named differently and may use a different range (more or less rows).

Range("E1").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("SUP_TOP_BOTTOM").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SUP_TOP_BOTTOM").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("F1:F58"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("SUP_TOP_BOTTOM").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
End Sub

 

[VoG]

Try

Dim LR As Long
LR = Range("F" & Rows.Count).End(xlUp).Row
Range("E1").Select
    Selection.AutoFilter
    ActiveSheet.AutoFilter.Sort.SortFields.Clear
    ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range _
        ("F1:F" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter

 

[ACF0303]

Works beautifully!

Thanks so much,
Cathy