How to write a macro not worksheet specific

Bob Spence 20 Reputation points

I have a workbook with 52 worksheets – named W01 to W52 each workbook is identical in layout (columns and rows)

I need a macro to sort the range C4:L255 by column C then by column D then by column G.

When I have tries to write these macro I either get the name of the worksheet in it or it want to sort the entire row. As shown in the 2 macros below

Sub Macro1()

' Macro1 Macro

    Range("C4:L255").Select

    ActiveWorkbook.Worksheets("W05").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("W05").Sort.SortFields.Add2 Key:=Range("C4:C255"), _

        SortOn:=xlSortOnValues, ORDER:=xlAscending, DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("W05").Sort.SortFields.Add2 Key:=Range("D4:D255"), _

        SortOn:=xlSortOnValues, ORDER:=xlAscending, DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("W05").Sort.SortFields.Add2 Key:=Range("G4:G255"), _

        SortOn:=xlSortOnValues, ORDER:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("W05").Sort

        .SetRange Range("C4:L255")

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    Range("C4").Select

End Sub

Sub SortOrder()

' SortOrder Macro

   ActiveSheet.Unprotect

    ActiveSheet.Unprotect

    Rows("4:255").Select

    Range("c4:L255").EntireRow.Sort Key1:=Range("C3"), Key2:=Range("D3"), Key3:=Range("G3"), Header:=xlNo      ' or xlYes

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True

0 comments No comments
Sign in to comment

1 answer

  1. Katerina-N 9,110 Reputation points • Microsoft External Staff • Moderator

    Hello Bob Spence,

    Thank you for posting your question in the Microsoft Q&A forum!

    I realize that you've encountered a problem with Marco Excel. I truly understand your confusion. Let me assist you further.

    For me to provide the most accurate solution, I just need to confirm that: Do you want the macro to sort only the active sheet or all week sheets (W01–W52), and does the range C4:L255 include a header row (so Header:=xlYes) or is it data only (Header:=xlNo)?

    And could you share a screenshot of the area around C4:L10 (or any small portion of the range), if possible? Please crop or blur out any personal or sensitive information first (names, emails, IDs, client data, etc.).

    I am looking forward for your respond. Thank you so much for your times.

    Best Regard,

    1. Katerina-N 9,110 Reputation points • Microsoft External Staff • Moderator

      Hello Bob Spence,

      I wish you all the best today! I am writing this to follow up and see if you need any additional help. Hope everything goes well. If you need further assistance, please feel free to tag me. I will be happy to assist you.

    Sign in to comment
Sign in to answer

Your answer