Duplicating worksheet error

[davez]

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.

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

 

[Michael M]

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

 

[davez]

Thanks for quick reply, but spaces or spelling are not the issue.

 

[rjwebgraphix]

If you're interested in alternate solutions you can try this....

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.

 

[davez]

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.

 

[rjwebgraphix]

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?

 

[Michael M]

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 !!!

 

[rjwebgraphix]

......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.

 

[Michael M]

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

 

[jozi86]

Check if there are any hidden sheets in your workbook.