Renaming Tabs using Master

onlyonekj

New Member
Joined
Jun 27, 2019
Messages
13
Hello,

I have actually posted this question before and received an answer that worked. I decided to use the formula again with another workbook and this time I am getting the
Run-time error '1004': Application-defined or object-defined error

The code I am trying to use is

VBA Code:
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 200 as 201
'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
End Sub

I've attached a picture of the workbook and the tabs that are showing, including the Master. Where am I going wrong? Any and all assistance is greatly appreciated. Thanks.
 

Attachments

  • TabNameChange.png
    TabNameChange.png
    90.2 KB · Views: 10

onlyonekj

New Member
Joined
Jun 27, 2019
Messages
13
Ok you have named ranges on the sheet being copied & they are workbook scope.
Do you need to keep those named ranges?
If so the best option is to delete them & then re-create them, but with sheet scope, rather than workbook scope.
Oh my...😲...I'm thinking sheet scope. Each worksheet will be for a new candidate but the template of each spreadsheet for the current tabs 1-200 are the same. They are altered when information from each candidates resume is typed into each section. I hope that makes sense. There are other tabs before tab 1 but they are contain information for the supervisor to use when scoring the candidates. One tab captures all of the scores entered into each of the spreadsheets (for each candidate). The candidate's scores roll over to this one spreadsheet (Matrix) capturing all scores at once. I hope I am making sense.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
In that case you will need to recreate them scoped to the sheet.
If they were scoped to the workbook, you may find that some of the sheets do not have those names.
Unfortunately I can't test that, as no matter how I create defined names, they get copied quite happily.
 

onlyonekj

New Member
Joined
Jun 27, 2019
Messages
13
In that case you will need to recreate them scoped to the sheet.
If they were scoped to the workbook, you may find that some of the sheets do not have those names.
Unfortunately I can't test that, as no matter how I create defined names, they get copied quite happily.
Ok. Thank you for helping me. I really really appreciate it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,237
Messages
5,594,988
Members
413,958
Latest member
nstevox

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
Top