Run-time error when using VBA to rename worksheets

[thomaslovell]

Please help!!!

Firstly, I am a VBA beginner. I have a Excel (2010) workbook where there are many sheets that need to be renamed according to a list of names on the first sheet named "Testing". The range of names (starting at cell A3) on the first sheet are a part of a Pivot Table. When the information in the Pivot Table is changed, I need to run the macro to change the Worksheet names automatically.

I have used the code below which was posted on another thread for someone requesting similar functionality.

While the below code seems to work fine, when the Pivot Table is updated (using a slicer) and I re-run the macro, I receive the following error message: Run-time error '1004': Application-defined or object-defined error.

Can anybody please tell me why this is happening???

Sub MakeSheetNames()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim rng1 As Range[/INDENT]
    Dim rng2 As Range
    Dim objDic
    Dim strTmp As String
    Dim strErr As String
    Dim lngCnt As Long
    Set objDic = CreateObject("scripting.dictionary")
    Set ws1 = ThisWorkbook.Sheets("Testing")
    If ws1.Index <> 1 Then
        MsgBox "This code assumes the Set-up sheet is the first worksheet, please reorder sheets and retry"
        Exit Sub
    End If
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    Set rng1 = ws1.Range(ws1.[a3], ws1.Cells(Rows.Count, "A").End(xlUp))
    lngCnt = 1
    For Each rng2 In rng1
        strTmp = CleanString(rng2.Value)
        If Len(strTmp) > 0 Then
            If Not objDic.exists(strTmp) Then
                lngCnt = lngCnt + 1
                If ThisWorkbook.Sheets.Count < lngCnt Then
                    MsgBox "You only have " & ThisWorkbook.Sheets.Count & " sheets for renaming" & vbNewLine & "Please add more sheets then rerun"
                Else
                    ThisWorkbook.Sheets(lngCnt).Name = strTmp
                    objDic.Add (strTmp), lngCnt
                End If
            Else
                strErr = strErr & strTmp & vbNewLine
            End If
        End If
    Next
    If Len(strErr) > 0 Then MsgBox "These sheets were duplicated:" & vbNewLine & strErr
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub


Function CleanString(strIn As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "[^A-Z\s]+"
        .Global = True
        .IgnoreCase = True
        CleanString = Application.Trim(.Replace(strIn, ""))
    End With
End Function

​

 

[Jerry Sullivan]

Hi Thomas,

One possibility is that the code is trying to rename a worksheet to a name that is already being used by another worksheet.

This could happen if after updating your pivotitems using the slicer, an item moved higher up the list.
Let's say item "China" moved from 2nd to 1st. When the code tries to rename the first sheet after "Testing" to "China" it fails because the second sheet after "Testing" is already named "China".

 

[thomaslovell]

Thanks for the reply Jerry!

Yes you are correct... I just did a test and this is definitely the problem!

What do you think is the best way to fix this?

As you said, let's say when the sheet name moves from 2nd to 1st, the macro won't run.

The only possibility I can think of is to clear all sheet names (to something irrelevant like "Name1", "Name2" etc.) before running the macro with the names I want. However, this is not user friendly, especially because other people will be viewing this workbook.

Do you have any other suggestions on how this could work?

Lastly, is it possible for the macro to run automatically when the names are changed? This would save me having to explain to other people who do not regularly use Excel how to 'run a macro' when viewing this workbook.

Thanks so much for your help!

 

[Jerry Sullivan]

I think clearing the sheet names with temporary names would be a good approach. The alternative of testing for names already existing and handling those differently seems unnecessarily complicated. The code should run quickly enough that users will not notice the temporary names. You can temporarily set Application.ScreenUpdating=False if needed.

The more complex aspects of your project are the exception handling.
What do you want to have happen if:
the number of items exceeds the number of sheets?
an item is not a valid sheet name?
the number of items is less than the last time the macro ran, leaving some unneeded sheets at the end?
duplicate items (your current code strips numbers so "Name1" and "Name2" both try to become sheet "Name")

You could use Event code to trigger running the macro when the names are changed. If the change is always driven by a change to the PivotTable slicer, you could use the event: Worksheet_PivotTableChangeSync

 

[thomaslovell]

Okay!

So, it seems the macro requires a number of steps:
1) Identify changes in Slicer (Worksheet_PivotTableChangeSync)
2) Name all worksheets with temporary names
3) Rename all worksheets with new names from Pivot Table

Do you agree? Are you able to provide the section of code I would need to perform the second step?

Regarding exception handling, it seems that some of these have been taken care of with the existing code. For example, when the number of names exceed the number of sheets, a pop-up box prompts the user to add more sheets. I believe the code also removes invalid symbols. The only problems I can see are your third and fourth points, if excess sheets are left at the end and if duplicate names are provided (this would only occur when giving the sheets temporary names). Is there are way to automatically add or remove sheets based on the number of names appearing in the Pivot Table?

Thanks Jerry.

 

[Jerry Sullivan]

Maybe this sequence of steps:
1. Macro is triggered by change in Slicer (Worksheet_PivotTableChangeSync)
2. Place in dictionary a list of unique items corresponding to resulting sheets
2.5 Handle duplicate names or invalid sheet names
3. Add or delete sheets
4. Name all worksheets with temporary names
5. Rename all worksheets with names from dictionary

If you want to learn VBA, it would be better if you take a try at writing the code first. I'll be glad to help if you get stuck.

The existing code does remove invalid symbols (it actually removes anything except Letters), but this still leaves you to decide what you want to have happen in cases like these:"
A. One of the PivotItems is "12-34" which is converted to "". The code skips creating a sheet for that item, with no warning or alternative name. That could be confusing to users.
B. Three of the PivotItems are "Name 1", "Name 2" and "Name 3". The existing code will only create one sheet for "Name". Also confusing.

Could you explain how this macro fits into your larger process? It's somewhat unusual to rename sheets each time a PivotTable reports filters are changed. Presumably there is some data on each of these sheets that would be modified at the same time. If you are making drill down tables for each item, there are simpler approaches you could consider.

 

[thomaslovell]

Do you care to explain what simpler methods I should investigate?

The Workbook has been designed to display physical testing information for a group of athletes. Each athlete will have a Worksheet with their name, displaying a chart with their testing data displayed. When the Pivot Table is updated, so are the charts corresponding to the athlete. The Worksheets need to be renamed according the athlete names displayed in the Pivot Table, as we may want to view the entire group of athletes, or maybe just one particular team. Because of this, the number (and order) of athletes will change depending on the selection on the Pivot slicers.

This is my first time using VBA. I have zero VBA skills at the moment and this is why I have 'stolen' this code from another thread. I am trying to learn however really don't even know where to start!?

Do you have any suggestions about where I should start learning about the code I need to write in this macro?

Also, there will never be numbers in the athlete names, so there shouldn't be a problem like the ones you mentioned as examples.

Thanks Jerry

 

[Jerry Sullivan]

Thomas,

You've picked a challenging first project for learning VBA.

For learning VBA, I'd encourage you to start with macros that perform simpler, single step tasks. Often you can use the macro recorder to get a good starting point based on a specific range of cells, then modify that recorded macro to make it more generalized for any range.

For you current project, I'd suggest simplifying the process to minimize the use of VBA and the complexity of the VBA that is used.

Techniques to do that include using Template sheet that could be copied and filtered for each athlete's detail instead of creating the detail and charts on blank sheets.

If the tool will be used interactively, consider creating a dashboard that allows the user to select any athlete or group of athletes (using slicers) to display the detail and chart for that selection on a single worksheet. Is there a need to generate let's say 10 separate sheets with data and charts instead of just giving the users the ability to easily call up that information without changing sheets?

 

[thomaslovell]

Hi Jerry,

I agree. This is a very challenging first project for VBA!

Yes, the macro recorder is useful to familiarise with how code is generated. However, I don't understand how this can help with learning how more complex code such as IF functions are generated. Can you provide some insight about this best way to learn this?

I understand your suggestion to simplify the build of this project, but my heart is set on the format where each athlete has an individual worksheet. This is important for large amount of information that will be viewed for each athlete's profile.

The code I originally posted is not too far away from what I need. I just need to figure out how to include a function where worksheet names are replaced with temporary names before the athlete names from the range of cells on the "Testing" sheet. Do you agree?

Do you know if Kutools for Excel can help with this function?

 

[Jerry Sullivan]

Yes, the macro recorder is useful to familiarise with how code is generated. However, I don't understand how this can help with learning how more complex code such as IF functions are generated. Can you provide some insight about this best way to learn this?

There's no shortage of information available on the topic; however each person learns differently. Some learn best from a tutorial book while others thrive from experimenting on their own using the resources like the Excel Developer Reference (Excel VBA Help) and online examples. Others learn best from taking a class or attending a seminar. What tools have you found worked best for you when you've learned other technology?

I understand your suggestion to simplify the build of this project, but my heart is set on the format where each athlete has an individual worksheet. This is important for large amount of information that will be viewed for each athlete's profile.

The code I originally posted is not too far away from what I need. I just need to figure out how to include a function where worksheet names are replaced with temporary names before the athlete names from the range of cells on the "Testing" sheet. Do you agree?

That's fine if you want to go that route. I can help with some code on the renaming sheets. You haven't explained how you are getting each athlete's data onto each sheet. That's more challenging than the renaming of the sheets. Please let me know your thoughts on this before I suggest some code to rename sheets, since those two things should be integrated.

Do you know if Kutools for Excel can help with this function?

I have not tried this add-in. Looking at the product description, I'm not seeing any tools that would help with the task of renaming the sheets. For your controlled data set (no numbers or special characters in the names), the renaming is relatively easy with VBA.