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
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,258
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,258
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
 

Forum statistics

Threads
1,081,523
Messages
5,359,263
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top