Rename Worksheets Using "Master" Worksheet

[onlyonekj]

Hello,

I need to change the name of the spreadsheets in the Workbook using a "Master" list created in the same Workbook. I tried to use the VBA below but got a 400 error code. I appreciate any assistance...I feel it is something simple I just cannot figure it out. I was going to attach the spreadsheet but I don't have permissions to do so. Thanks.

Worksheets Currently Named: .73, .74, .75, .76, .77, through .200
Need to Change Worksheet Names to: .74, .75, .76, .77, through .200

Sub ChangeTabNames()
Dim rng As Range, i As Integer
Application.ScreenUpdating = False
For Each rng In Worksheets("Master").Range("A:A201")
i = i + 1: Sheets("." + i).Name = rng.Value
Next rng
End Sub

 

[Snakehips]

Firstly, just to understand the task....
Existing sheet name .73 will become .74 , existing .74 will become .75. etc ??
So sequential numbers 74 :200
What becomes of old .200?

You have these 'new' names in Master column A ?

Clarify the above and you will get assistance.


Comments on your code.
Application.ScreenUpdating = False. Always ensure you reinstate at the end of code with Application.ScreenUpdating = True

Your specified range needs a starting row eg. Range("A1:201")
Your variable i starts at 0, ???If you are going to rename and use similar names to existing then you may need to work backwards through the sheets or use a dummy in order to avoid any duplicate name error.

 

[JLGWhiz]

If you have the names you want to use for your worksheets listed in column A then you can just use a For loop to rename the existing sheets, provided they are in the same order as the list. Say you want to start with listed on row 6 of Column A. It would be sheet 5 in the workbook.

Sub t()
For i = 5 To 199    Sheets(i).Name = Sheets("Master").Range("A" & i + 1).Value
Next
End Sub

But like Snakehips pointed outs, it is not clear what you have in column A or if you want to change all of your sheet names or just a few.

 

[onlyonekj]

I have added the URL to the spreadsheet. I hope this works. I downloaded the Forum Tools Add-in but could not get it to work properly. Column A shows what I want the worksheets to be renamed to. For instance cell A1=.74 it will replace tab .73; cell A2=.75 it will replace tab .74; so forth and so on. Thanks for your assistance.

https://www.dropbox.com/s/9dd3167o1domkgm/Rename Worksheets.JPG?dl=0

 

[Snakehips]

@onlyonekj I assume that you have data to preserve in these sheets otherwise you would just delete '.73' and add a new last sheet?

Anyway, try...

Sub ookj()

'Assumes all sheets bar Master are to be renamed as per a full listing in column A
'Because of the potential for duplicate name error in renaming .73 as .74
'Start with the last sheet and work backwards


Application.ScreenUpdating = False
For i = Sheets.Count To 2 Step -1
Sheets(i).Name = Sheets("Master").Range("A" & i - 1).Value
Next
Application.ScreenUpdating = True

Hope that helps.

 

[onlyonekj]

Snakehips...thank you so much. Yes, I did want to preserve my that data on the spreadsheets. It worked like a charm...I am so excited right now... I really appreciate it.

 

[Snakehips]

Great. You are most welcome.