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

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,757
Office Version
  1. 365
Platform
  1. Windows
How many sheets do you have & what is the last row with data in col A of the master sheet?
 

onlyonekj

New Member
Joined
Jun 27, 2019
Messages
13
How many sheets do you have & what is the last row with data in col A of the master sheet?
Hi Fluff,

There are currently 200 tabs (1-200) and in Column A of the Master, data is listed in A1:A100 (numerical order 201-300).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,757
Office Version
  1. 365
Platform
  1. Windows
In that case that's the reason, the code will initially be looking at A199 for a sheet name, but it's blank.
 

onlyonekj

New Member
Joined
Jun 27, 2019
Messages
13

ADVERTISEMENT

In that case that's the reason, the code will initially be looking at A199 for a sheet name, but it's blank.
I must admit I am a true novice...I know just enough to mess up a spreadsheet. I initially added worksheets after worksheet 200. The VBA I used returned worksheets named "200(1)", "200(2)", "200(3)"...all the way to "200(100)". So then I attempted to rename the tabs using the aforementioned VBA and that's where it went left...as lost as a bat in the daytime. Below is VBA I used to add the worksheets and a picture of what the original tabs on the worksheet look like before attempting to add and rename worksheets. I really need a VBA that would add and rename the worksheets at one time. Is there a way to do that?

VBA Code:
Sub CopySheetMultipleTimes()
Dim n As Integer
Dim i As Integer
On Error Resume Next

    n = InputBox("How many copies do you want to make?")

    If n > 0 Then
        For i = 1 To n
            ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
        Next
    End If

End Sub
 

Attachments

  • Original Spreadsheet.png
    Original Spreadsheet.png
    234.3 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,757
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub onlyonekj()
   '1148831
   Dim i As Long, StartNo As Long
   Dim n As Variant
   
   n = InputBox("How many copies do you want to make?")
   If n = "" Then Exit Sub
   Application.ScreenUpdating = False
   StartNo = Sheets.Count
   For i = 1 To n
      ActiveSheet.Copy , Sheets(StartNo + i - 1)
      ActiveSheet.Name = StartNo + i - 1
   Next
   Application.ScreenUpdating = True
End Sub
This relies on the the last sheet number being 1 less than the total number of sheets.
 

onlyonekj

New Member
Joined
Jun 27, 2019
Messages
13

ADVERTISEMENT

How about
VBA Code:
Sub onlyonekj()
   '1148831
   Dim i As Long, StartNo As Long
   Dim n As Variant
  
   n = InputBox("How many copies do you want to make?")
   If n = "" Then Exit Sub
   Application.ScreenUpdating = False
   StartNo = Sheets.Count
   For i = 1 To n
      ActiveSheet.Copy , Sheets(StartNo + i - 1)
      ActiveSheet.Name = StartNo + i - 1
   Next
   Application.ScreenUpdating = True
End Sub
This relies on the the last sheet number being 1 less than the total number of sheets.
The code works, however, instead of renaming the tab after Tab 200 "201", it names it "208" because of the other 7 tabs in the worksheet. They are not numbered but have names. It also asks for each spreadsheet if I want to rename each section the new spreadsheet or leave it "as-is". I want them spreadsheet to copy "as-is" without having to say yes or no for each area on the spreadsheet. Is there a cleaner way to copy/rename the spreadsheets? A way that won't ask whether or not each section needs to be renamed or kept "as-is" as well as having the next new tab be renamed to "201" so forth and so on until the last tab is created (tab "300")? Thanks.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,757
Office Version
  1. 365
Platform
  1. Windows
instead of renaming the tab after Tab 200 "201", it names it "208" because of the other 7 tabs in the worksheet.
That's why I said
This relies on the the last sheet number being 1 less than the total number of sheets.
You never made any mention of the other sheets & the are not visible in any of images. ;)
It also asks for each spreadsheet if I want to rename each section the new spreadsheet or leave it "as-is".
Can you please explain what you mean by this?
 

onlyonekj

New Member
Joined
Jun 27, 2019
Messages
13
Hello Fluff,

I took snapshots of the questions I am being asked before the spreadsheet(s) are added to the workbook. I am not sure it is asking these questions instead of just copying/pasting/renaming the worksheets. If I haven't said it already ready, thank you for your assistance with this.
1603219971356.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,757
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,246
Messages
5,595,044
Members
413,963
Latest member
teggl97

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