Renaming sheets using list on another sheet

[lembi2001]

Thank you for your help with my previous query


My Workbook is taking shape quite well now but I have come across another stumbling block.


The workbook contains a Worksheet called Data and this sheet contains information about sheet names.


The list contains 3 columns (A:C) which detail the sheet name.


Column A is the identifier, a 4 digit code, Column B is a further identifier and Column C is the name of the sheet. What I am trying to do is rename the sheets in my workbook based on their original name.


Example


All the sheets are named as 4 digit codes when they are created
As part of another Macro I have, I want to read the sheet name, find that value in the Data sheet and rename the spreadsheet using the information in columns A B and C


Pseudo Code:


Get Sheet name
Find Sheet Name in Data Sheet
Rename Sheet using DataSheet Row A & " - " & Datasheet row B & " - " & DataSheet Row C


The result would look something like 3091 - U4 - Unit 4


Anyone able to firstly understand my gibberish and secondly help me out??

 

[FormR]

Re: Reanming sheets using list on another sheet

Hi, how about instead of getting the sheet name and looking up the details in the data sheet, we loop through the values in the data sheet and get the sheet and re-name from there?

Sub renamesheets()
Dim c As Range, ws As Worksheet
With Worksheets("Data")
    For Each c In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        On Error Resume Next
        Set ws = Worksheets(CStr(c.Value))
        On Error GoTo 0
        If Not ws Is Nothing Then ws.Name = c.Value & " - " & c.Offset(, 1).Value & " - " & c.Offset(, 2).Value
    Next c
End With
End Sub

 

[lembi2001]

Re: Reanming sheets using list on another sheet

Thanks for the reply

If I try and run this code I get an error 400.

If I step into it and run it line by line manually it completes about 7 runs and then throws error

Run-time error '1004':
Application-defined or object-defined error

 

[FormR]

Re: Reanming sheets using list on another sheet

Hi, when you click debug what line of code is highlighted?

 

[lembi2001]

Re: Reanming sheets using list on another sheet

Interestingly it crashes when it actually tries to rename the sheet. The sheet is renamed and then the macro throws the error.

If Not ws Is Nothing Then ws.Name = c.Value & " - " & c.Offset(, 1).Value & " - " & c.Offset(, 2).Value​

 

[FormR]

Re: Reanming sheets using list on another sheet

Try this slight modification.

Sub renamesheets()
Dim c As Range, ws As Worksheet
With Worksheets("Data")
    For Each c In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        On Error Resume Next
        Set ws = Worksheets(CStr(c.Value))
        On Error GoTo 0
        If Not ws Is Nothing Then ws.Name = c.Value & " - " & c.Offset(, 1).Value & " - " & c.Offset(, 2).Value
        Set ws = Nothing
    Next c
End With
End Sub

 

[lembi2001]

Re: Reanming sheets using list on another sheet

Grrrr

Unfortunately that doesn't change it either.

This is the code I have (just copied and pasted and change A2 to A1 as my data list has no header row)

Sub renamesheets()
Dim c As Range, ws As Worksheet
With Worksheets("Data")
    For Each c In .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        On Error Resume Next
        Set ws = Worksheets(CStr(c.Value))
        MsgBox ws
        On Error GoTo 0
        If Not ws Is Nothing Then ws.Name = c.Value & " - " & c.Offset(, 1).Value & " - " & c.Offset(, 2).Value
        Set ws = Nothing
    Next c
End With
End Sub

List of Macros - https://ibb.co/gKeXvS

Running the renamesheets macro from above window - https://ibb.co/jUFi27

Stepping through the macro before the next error - https://ibb.co/mB6qh7

Error 1004 - https://ibb.co/mP8E9n

No idea what is causing this, but thank you very much for helping me with this.

 

[FormR]

Re: Reanming sheets using list on another sheet

When you get the error and are in debug mode what do the following return from the immediate window?

?  c.Value & " - " & c.Offset(, 1).Value & " - " & c.Offset(, 2).Value

And

? ws.name

What version of Excel are you using?

 

[lembi2001]

Re: Reanming sheets using list on another sheet

Watch :   :         c.Value & " - " & c.Offset(, 1).Value & " - " & c.Offset(, 2).Value : "1007 -  - FIRE, BOILERS, WATER TANKS & LIFTS" : Variant/String : ThisWorkbook.renamesheets

Watch :   :         ws.Name : "1007" : String : ThisWorkbook.renamesheets

I am using Excel 2016

Thanks again

 

[FormR]

Re: Reanming sheets using list on another sheet

Hi, the sheet name can only be a maximum of 31 characters - you'll need to rethink your naming convention.