Automatically update macro when sheet name changes

SaraB802

New Member
Joined
Feb 7, 2013
Messages
34
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
There's lots of ways to refer to various sheets -

A quick recorded macro sorting a couple of columns looks like this:
Rich (BB code):
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.
Rich (BB code):
    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
 
Last edited:

SaraB802

New Member
Joined
Feb 7, 2013
Messages
34
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

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,871
Messages
5,574,746
Members
412,617
Latest member
mlharris
Top