Loop Macro on all worksheets

[shebe228]

Can someone help me loop this macro on all worksheets? (theres 47)

Sub AM2_Format_Util_Template()
'
' AM2_Format_Util_Template Macro
'


'
    Columns("AC:AC").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("W:W").Select
    ActiveSheet.Paste
    Columns("AG:AG").Select
    ActiveSheet.Paste
    Columns("A:T").Select
    Range("T1").Activate
    Selection.EntireColumn.Hidden = True
    Columns("U:AC").Select
    Range("AC1").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Columns("AD:AD").Select
    Selection.Insert Shift:=xlToRight
    Columns("AO:BB").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("BD:BD").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("AMC").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("AMC").Sort.SortFields.Add Key:=Range("BG2:BG38090" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("AMC").Sort
        .SetRange Range("BD1:BQ38090")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("AO:BB").Select
    ActiveWorkbook.Worksheets("AMC").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("AMC").Sort.SortFields.Add Key:=Range("AQ2:AQ38091" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("AMC").Sort
        .SetRange Range("AO1:BB38091")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll ToRight:=-15
    Columns("AE:AL").Select
    Range("AL1").Activate
    ActiveWorkbook.Worksheets("AMC").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("AMC").Sort.SortFields.Add Key:=Range("AL2:AL38092" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("AMC").Sort.SortFields.Add Key:=Range("AH2:AH38092" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("AMC").Sort
        .SetRange Range("AE1:AL38092")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("V:AC").Select
    Range("AC1").Activate
    ActiveWorkbook.Worksheets("AMC").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("AMC").Sort.SortFields.Add Key:=Range("AC2:AC38093" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("AMC").Sort.SortFields.Add Key:=Range("X2:X38093") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("AMC").Sort
        .SetRange Range("V1:AC38093")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[MARK858]

I do need to add the loop too

What do you want the loop to do (you haven't stated it still), loop through all the sheets again?

I meant change my sheet name to refer to the active sheets

What active sheets? only one sheet can be active at a time and you don't need to activate sheets to work with them.

Again explain exactly what you want the code still to do (the term active sheets doesn't need to be in the explanation).

 

[shebe228]

Yes. I want to loop through all sheets.

In the original code, the sheet was referred to by name which is what I was attempting to remove. When I did, it messed everything up.

Back to what you said in your previous reply, I can copy and paste values before sorting if it will be an issue.

 

[MARK858]

Possibly....

Sub AM5_IN_Filter_Discounts()
    '
    ' AM5_IN_Filter_Discounts Macro
    ' Adds two new columns with if statements for less than 10%.  Sorts by Yes-Less than 10% then by Bill Count and Allowed
    '
    Dim ws As Worksheet
    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets

        With ws
            .Columns("BC:BC").Insert Shift:=xlToRight

            .Range("BC1").Value = ">10%"

            With .Range("BC2:BC3809")
                .FormulaR1C1 = "=IF(RC[-9]>10%,""yes"",""no"")"
                .Value = .Value
            End With

            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("BC2"), SortOn:=xlSortOnValues, _
                                 Order:=xlAscending, CustomOrder:="no,yes", DataOption:=xlSortNormal
            With .Sort
                .SetRange Parent.Range("AO1:BC38068")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With


            .Sort.SortFields.Add Key:=.Range("AQ2"), SortOn:=xlSortOnValues, _
                                 Order:=xlDescending, DataOption:=xlSortNormal

            With .Sort
                .SetRange Parent.Range("AO1:BC38069")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

            .Columns("BC:BC").Copy .Columns("BS:BS")

            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("BS2" _
                                             ), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="no,yes", _
                                             DataOption:=xlSortNormal
            .Sort.SortFields.Add Key:=.Range("BH2" _
                                             ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

            With .Sort
                .SetRange Parent.Range("BE1:BS38070")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

        End With
    Next
    Application.ScreenUpdating = True
End Sub

 

[shebe228]

Thank you for your help. I'll have to check it tomorrow. My brain is officially fried for the day.