Automatically update macro when sheet name changes

[SaraB802]

So I have a macro, very simply recorded which sorts the data on various worksheets within a workbook. When someone changes the name of one of the worksheets the macro breaks, as it is looking for the old name, not the new one. Is there away that the macro can be updated automatically with the name change of the worksheet, as normal excel formula references would?

Unfortunately I suspect not, but if you don't ask you don't find these things out.

Many thanks

 

[Darren Bartrup]

There's lots of ways to refer to various sheets -

A quick recorded macro sorting a couple of columns looks like this:

Range("A2:B12").Select    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A12") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:B12")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

The main part referencing a sheet is ActiveWorkbook.Worksheets("Sheet1") and this is the part of code that needs to change to reference multiple worksheets.

If you hold a reference to each worksheet within a variable then this can be used to reference lots of sheets.
Simply change the reference to a single sheet to a variable that can hold a reference to different sheets.

    Dim wrkSht As Worksheet
    
    For Each wrkSht In ActiveWorkbook.Worksheets
        wrkSht.Sort.SortFields.Clear
        wrkSht.Sort.SortFields.Add Key:=Range("A2:A12") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With wrkSht.Sort
            .SetRange Range("A2:B12")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next wrkSht

 

[SaraB802]

Darren - thanks that looks like it would solve the problem if it was all the work sheets in the book, but it is not all of them, just a selection (approx. half of them)

 

[Darren Bartrup]

Again, a number of ways to solve the problem. If sheets are next to each other you could refer to them by index number - ignore sheets 1 to 7.
If not you can refer to them by name, this code will sort Sheet1 and Sheet3, it will pop up a message on Sheet2 and ignore any other sheets.

    Dim wrkSht As Worksheet
    
    For Each wrkSht In ActiveWorkbook.Worksheets
        Select Case wrkSht.Name
        
            Case "Sheet1", "Sheet3"
                wrkSht.Sort.SortFields.Clear
                wrkSht.Sort.SortFields.Add Key:=Range("A2:A12") _
                    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                With wrkSht.Sort
                    .SetRange Range("A2:B12")
                    .Header = xlGuess
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
            
            Case "Sheet2"
                MsgBox "On sheet 2"
            
            Case Else
                'Do nothing
        End Select
    Next wrkSht