Dec 20 2022 12:09 PM
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?
Dec 20 2022 12:27 PM
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...
Dec 20 2022 12:31 PM
Dec 20 2022 01:17 PM
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.