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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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:
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top