Duplicating worksheet error

davez

Board Regular
Joined
Feb 12, 2003
Messages
144
Office Version
  1. 365
Platform
  1. Windows
Hi all, at my wits end with this problem so hoping someone can help -

Using Excel2007 & I have the following code which I hope to use to create duplicates of a worksheet named 'prec' based on a list of names at cell B21 down in a worksheet named 'CODES' , but keep getting the message 'subscript out of range', with the line Set rngName = ThisWorkbook.Sheets("CODES").range("B21") highlighted.

Thanks for any help provided.


Code:
Sub duplicate_prec_based_on_list()
   Dim rngName As range
   Dim i As Integer
   Set rngName = ThisWorkbook.Sheets("CODES").range("B21")
   Do Until rngName.Value = ""
       i = ThisWorkbook.Sheets.count
       Sheets("prec").Copy After:=Sheets(i)
       ThisWorkbook.Sheets(i + 1).Name = rngName.Value
       Set rngName = rngName.Offset(1)
   Loop
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Your code works fine for me.
Check to make sure that:
1. your sheet name does not have any leading trailing spaces
2. your sheet name is spelled exactly the same as the sheet name in the code
 
Upvote 0
Thanks for quick reply, but spaces or spelling are not the issue.
 
Upvote 0
If you're interested in alternate solutions you can try this....

Code:
Sub Dupe_Prec()

    Dim lr As Long
    Dim i As Long
    Dim c1 As Range, c2 As Range, rng As Range
    Dim cel As Variant
    
    lr = ThisWorkbook.Sheets("CODES").Range("B65536").End(xlUp).Row
    Set c1 = ThisWorkbook.Sheets("CODES").Cells(21, "B")
    Set c2 = ThisWorkbook.Sheets("CODES").Cells(lr, "B")
    Set rng = ThisWorkbook.Sheets("CODES").Range(c1, c2)
    
    
    For Each cel In rng
        i = ThisWorkbook.Sheets.Count
        Sheets("prec").Copy After:=Sheets(i)
        ThisWorkbook.Sheets(i + 1).Name = cel.Value
    Next cel

End Sub

I just prefer for loops over do loops wherever possible. Depending on how many rows you're dealing with, for loops seem to run a lot faster than do loops.
 
Upvote 0
Thanks jr, however I am getting the same 'subscript out of range' error as with the other code.

Out of interest/frustration I setup a new blank workbook with sheets names A & B, and changed the code to reflect the new names & still get the same error with both sets of code.

If anyone can suggest where to look next a fixing this, it would be much appreciated.
 
Upvote 0
Can you post the data in Column B (or some of it)? There are restrictions on what sheet names can be. That's the only thing I can think of. My test of your original code and my code both worked on single word only names. I'm guessing the problem is in what you're trying to name it.

Edit: What line did my code fail on?
 
Last edited:
Upvote 0
I thought the same thing....but the code shouldn't fail at the line specified by davez, if the sheet names are incompatible.
I just tested the original code with 50 sheets in the range on the CODES sheet with no problems
I know this sounds a little bizarre, but have you tried closing Excel completely and starting afresh.
I had a FOR / NEXT loop recently that simply would not go to the last row in a column......I tried everything, to no avail.
I closed and reopened Excel....still no good......ended up rebooting the darn computer, and rewrote the code...worked fine !!!
 
Upvote 0
......ended up rebooting the darn computer, and rewrote the code...worked fine !!!

I too have had bizarre issues running code that just wouldn't work right until I copied the code off to notepad. Saved it, reboot, start a new workbook, copied the exact same code into the new workbook from the text file and everything was fine, but if I continued working in the original workbook, it would never work. One of those things that has you beating your head on the table for hours till you decide to do something drastic.
 
Upvote 0
I've been using this and it seems to work pretty well.
I have no affiliation with the product or it's creators, but it sure saves a lot of heartache.

VBA Code Cleaner
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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
Back
Top