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]

The error message in your video indicates the problem is still merged cells. What did the debug.print messages from the debug code I recommended in my last post tell you?

Are you aware of what the "immediate" window in the VBE debugger is? You were not using it in your video.

 

[rlv01]

Excel Tips: Why You Should Avoid Merging Cells

Use the Center Across Selection feature to easily merge cells in Excel.

edu.gcfglobal.org

Excel: Don’t Merge Those Cells! Here is an Alternative. – Office Bytes

 

[rlv01]

 

[Saher Naji]

Thanks you very much,
That's great, I found it, it's Feb_29 sheet, but it was hidden tab
Now I just need to stop moving to Feb_1 after clicking on the VBA button, I want to stay in the same sheet

You are amazing !

 

[Saher Naji]

Excel Tips: Why You Should Avoid Merging Cells

Use the Center Across Selection feature to easily merge cells in Excel.

edu.gcfglobal.org

Excel: Don’t Merge Those Cells! Here is an Alternative. – Office Bytes

First time I've had a problem with merged cells
very good tips
thank you

 

[rlv01]

after clicking on the VBA button, I want to stay in the same sheet

You said in your original post that you wanted the macro to act on all the sheets. If you just want it to act on the active sheet. Then revert to something like this:

Sub SortActiveSheet()
    Dim WS As Worksheet

    Set WS = ActiveSheet

    With WS.Sort
        If Not WS.Range("C3:J43").MergeCells Then
            .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
        Else
            MsgBox "Error - sort range contains merged cells", vbCritical, "Sorting worksheet: " & WS.Name
        End If
    End With
End Sub

 

[Saher Naji]

I used some parts from your last code, every thing is working well now
You are right, It's better to work on the active sheet one by one, not necessary to make the code run in all sheets each time
thank you very much

This is the updated code:

Sub SortActiveSheet()
    Dim WS As Worksheet

    Set WS = ActiveSheet

    With WS.Sort
        If Not WS.Range("C4:J43").MergeCells Then
            .SortFields.Clear
            .SortFields.Add2 Key:=WS.Range("G3:G43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange WS.Range("C3:J43")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        Else
            MsgBox "Error - sort range contains merged cells", vbCritical, "Sorting worksheet: " & WS.Name
        End If
    End With
End Sub