Making the VBA code affect a specific range of pages

[Saher Naji]

Hello,
I have this very simple code

Sub SortByTime()
'
' SortByTime Macro
' Sort by Time
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    Range("C4:J43").Select
    ActiveWindow.SmallScroll Down:=-36
    ActiveWorkbook.Worksheets("Jan_1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Jan_1").Sort.SortFields.Add2 Key:=Range("G4:G43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Jan_1").Sort
        .SetRange Range("C3:J43")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

what I'm trying to do is make this code affect on 365 sheets, (not all of the workbook sheets) I have 30 sheets for each month,
I did not know how to edit this code,
I tried to add the sheets like this,
With ActiveWorkbook.Worksheets("Jan_1"+"Jan_2"+"Jan_3"+"Jan_4"+"Jan_5"+.... etc ).Sort

but it did not work

Thanks in advance

 

[rlv01]

Maybe something like this.

Sub SortSheets()
    Dim WB As Workbook
    Dim WS As Worksheet

    'Set WB = ThisWorkbook                             'Pick one
    Set WB = ActiveWorkbook                           'Pick one

    For Each WS In WB.Worksheets
        Select Case WS.Name
        Case "Main", "Summary", "Index"               '<- This is the 'ignore' list. List worksheets here that you DO NOT want to sort
        Case Else
            With WS.Sort
                .Sort.SortFields.Clear
                .Sort.SortFields.Add2 Key:=WS.Range("G4:G43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SetRange WS.Range("C3:J43")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End Select
    Next WS
End Sub

 

[Saher Naji]

Unfortunately it didn't work, there might be something wrong,

 

[rlv01]

1.png references your "SortByTime" macro, not the one I posted ("SortSheets").
2.png - an error like this applies to a specific line of code. You should supply that information.

Try this.

Sub SortSheets()
    Dim WB As Workbook
    Dim WS As Worksheet

    'Set WB = ThisWorkbook                             'Pick one
    Set WB = ActiveWorkbook                           'Pick one

    For Each WS In WB.Worksheets
        Select Case WS.Name
        Case "Main", "Summary", "Index"               '<- This is the 'ignore' list. List worksheets here that you DO NOT want to sort
        Case Else
            With WS.Sort
                .SortFields.Clear
                .SortFields.Add2 Key:=WS.Range("G4:G43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SetRange WS.Range("C3:J43")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End Select
    Next WS
End Sub

 

[Saher Naji]

1.png references your "SortByTime" macro, not the one I posted ("SortSheets").
2.png - an error like this applies to a specific line of code. You should supply that information.

Try this.

Sub SortSheets()
    Dim WB As Workbook
    Dim WS As Worksheet

    'Set WB = ThisWorkbook                             'Pick one
    Set WB = ActiveWorkbook                           'Pick one

    For Each WS In WB.Worksheets
        Select Case WS.Name
        Case "Main", "Summary", "Index"               '<- This is the 'ignore' list. List worksheets here that you DO NOT want to sort
        Case Else
            With WS.Sort
                .SortFields.Clear
                .SortFields.Add2 Key:=WS.Range("G4:G43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SetRange WS.Range("C3:J43")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End Select
    Next WS
End Sub

This line.
.SortFields.Clear

 

[rlv01]

This line.
.SortFields.Clear

Not when I run it. Are you sure you are running the new code I posted? What is the error message?

 

[Saher Naji]

I deleted the old macro, and used your code, it's working very well, thank you very much
but I see this message each time, although this range ("C3:J43") do not have any merged cells!
can you help me get rid of it?

Now this is the code:
We need to update it:

Sub SortSheets()
    Dim WB As Workbook
    Dim WS As Worksheet

    Set WB = ThisWorkbook

    For Each WS In WB.Worksheets
        Select Case WS.Name
        Case "INDEX", "DATA", "TOTALS", "NOTIFICATIONS", "C.C JAN", "January_2023", "C.C FEB", "February_2022"              '<- This is the 'ignore' list. List worksheets here that you DO NOT want to sort
        Case Else
            With WS.Sort
                .SortFields.Clear
                .SortFields.Add2 Key:=WS.Range("G4:G43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SetRange WS.Range("C3:J43")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End Select
    Next WS
End Sub

 

[rlv01]

When a runtime error occurs, two pieces of information are needed: the error number/message and the line of code that the error occurs on. In your case, a 3rd piece of information is needed: the name of the worksheet being processed. The error message suggests you have merged cells in one of the worksheets being sorted, even if you do not think that you do.

One suggestion is to add some debug code to note which worksheet is being processed when the error occurs. The Debug.Print statements will send the information to the debugger "Immediate" window.

Sub SortSheets()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim Msg As String

    Set WB = ThisWorkbook

    For Each WS In WB.Worksheets
        Select Case WS.Name
        Case "INDEX", "DATA", "TOTALS", "NOTIFICATIONS", "C.C JAN", "January_2023", "C.C FEB", "February_2022"    '<- This is the 'ignore' list. List worksheets here that you DO NOT want to sort
        Case Else
            With WS.Sort
                'For debug
                WS.Activate
                If Not WS.Range("C3:J43").MergeCells Then
                    Msg = " (no merged cells in sort range)"
                Else
                    Msg = " (contains merged cells)"
                End If
                Debug.Print "Sorting worksheet: " & WS.Name & Msg
                Application.StatusBar = "Sorting worksheet: " & WS.Name
                'end debug

                .SortFields.Clear
                .SortFields.Add2 Key:=WS.Range("G4:G43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SetRange WS.Range("C3:J43")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End Select
    Next WS
End Sub

 

[Saher Naji]

Hello again,
Sorry for being late
This what exactly happened with this VBA code:

The code does its job well, but there are some negative effects
1- It moves from the current sheet to another sheet, but I want to stay on the current sheet
2- Also an error message comes up but I still don't know why?
I think this screenshot clarifies the issue

VBA - Create and share your videos with Clipchamp

Watch videos made with Clipchamp, the best video maker for everyone.

clipchamp.com

Thank you

 

[Saher Naji]

I unmerged all merged cells, to test the code, but that didn't work
and in fact, I need to merge some cells later