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

 

[davez]

Well, I rebooted the pc, started from scratch & all is working without issue, guess some gremlins got in the works somewhere, still very puzzling but will put it down to experience.

Thanks for all replies & assistance.

 

[Michael M]

Yeah, it happens....consider using the code cleaner link that I posted.
It will save you some pain, I guarantee....