VBA code is sorting on some worksheet but not all

New Contributor

i have a vba code to search out a worksheet and then sort by date. it was working on the original 15 sheets i had in the workbook. when i copied and added a 16th worksheet it does not run the sort on that sheet. microsoft excel 365 mso version 2211                                                                    With Workbooks("30#group.xlsm").Worksheets("22").Sort
.SetRange Range("F10:L608")
.Header = x1Yes
.MatchCase = False
.Orientation = x1TopToBottom
.SortMethod = x1PinYin
.Apply
End With

 

it seems to me that when i added the 16th sheet that it does not see it. the 15th sheet is (code sheet 23) or that the new (16th) worksheet can not run the vba code?

4 Replies

@Arcade_Printing 

If you change the line

 

With Workbooks("30#group.xlsm").Worksheets("22").Sort

 

to

 

With ActiveSheet.Sort

 

you should be able to run the macro on the active sheet, whichever sheet that is...

Could you add a screenshot of the list of sheets in the VBA editor under the VBA Project pane?

@Arcade_Printing 

 

Maybe consider replacing your existing code with a loop through all the sheets, rather than repeating the code for each sheet. This will make it easier to keep consistent and debug. 

 

You could try this (not tested):

 

Public Function SortSheetsInGroup30()

Dim wb As Workbook
Dim ws As Worksheet
Dim sheetsToSort() As String
Dim addressToSort As String

addressToSort = "F10:L608"

'put any sheet names you want to sort comma-separated in the first parameter of Split
sheetsToSort = Split("14,18,11,20,20.5,23,24,25,26,27,28.5,29.25,30,33,0,22,12.5", ",")

Set wb = Workbooks("30#group.xlsm")

For Each ws In wb.Worksheets

    If Not IsError(Application.WorksheetFunction.Match(ws.Name, sheetsToSort, 0)) Then
        'the sheet is in the sheetsToSort array
    
        'sort it
        With ws.Sort
            .SetRange ws.Range(addressToSort)
            .Header = x1Yes
            .MatchCase = False
            .Orientation = x1TopToBottom
            .SortMethod = x1PinYin
            .Apply
        End With
    
    End If

Next ws

End Function

 

Call that function from your OnChange event in the control workbook.