How to write a macro not worksheet specific

Bob Spence 20 Reputation points
2026-01-29T15:44:02.59+00:00

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

 

Microsoft 365 and Office | Excel | For home | MacOS
0 comments No comments

1 answer

Sort by: Most helpful
  1. Katerina-N 9,295 Reputation points Microsoft External Staff Moderator
    2026-01-29T23:32:20.1166667+00:00

    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,

    Was this answer helpful?


Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.