Renaming Tabs using Master

[onlyonekj]

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

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.

 

[Fluff]

How many sheets do you have & what is the last row with data in col A of the master sheet?

 

[onlyonekj]

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]

In that case that's the reason, the code will initially be looking at A199 for a sheet name, but it's blank.

 

[onlyonekj]

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?

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

 

[Fluff]

How about

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]

How about

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]

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]

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.

 

[Fluff]

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.