Help Understanding Code Dealing with Creating New Spreadsheets

[yerromnitsuj]

I'm still fairly new to VBA and I am trying to figure out some code that someone else wrote. Here it is:

Sub Make_State_sheets()

Dim New_sheet As Worksheet

Set New_sheet = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
New_sheet.Name = "X2"

For i = 1 To Worksheets(SheetA).Range("B3").Value

Sheets("X").Select
Sheets("X").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Worksheets(SheetB).Cells(i + 1, 1)

Next i

Application.DisplayAlerts = False
Sheets("X").Delete
Application.DisplayAlerts = True

End Sub

I'm not positive about what this code does, but my best guess is that it's supposed to create new worksheets called X, X2, X3, X4, etc. up to i, which is the value in cell B3 on SheetA. Then again, if that's the case, I'm not sure how the DisplayAlerts part at the bottom is supposed to come into play.

I have a bunch of worksheets, and the last one is named "X". When I run the macro, it creates a new worksheet entitled "X2", then tries to create a new worksheet and I get the error "Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic."

What is your best guess as to what this macro is supposed to be doing? Why might I be getting the error I'm getting? Thanks for the help!

 

[yerromnitsuj]

The following might be a simpler description of what I need help with:

I'm trying to decipher a portion of code. The workbook already has a sheet X. When the code gets to this point it creates a new sheet called X and I get an error stating that I can't name a new sheet the same name as an existing sheet. Can someone help me to understand what this code seems to be trying to do and why i might be getting that error? Thanks!

For i = 1 To Worksheets(SheetA).Range("B3").Value

Sheets("X").Select
Sheets("X").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Worksheets(SheetB).Cells(i + 1, 1)

 

[daverunt]

The macro creates a number of sheets based on a value in sheet A cell B3. So sheet A cell B3 = 3 you get 3 new sheets created when the macro runs.
(For i = 1 To Sheets("SheetA").Range("B3").Value)

Sheet X is used for the copy - perhaps its a template the user wanted.

The copy is named X(2) (as it would be if you manually copied X) and becomes the ActiveSheet

The macro then renames the ActiveSheet based on values in sheet B Cells (i+1, 1) - which is whatever is found in sheet B Column A row (i +1).

The reason for the failure is you have already created sheets based on the values in those cells and are trying to create the same named sheets again by re-running the macro - hence the warning.

You could delete the sheets that have been created and run it again.

 

[yerromnitsuj]

Thanks! I figured that out just before you sent your response. Now I am getting the error "copy method of worksheet class failed". Any ideas as to why this is happening?

 

[daverunt]

Interesting. I Googled : copy method of worksheet class failed - and found this.

http://support.microsoft.com/kb/210684

The most interesting bit being: Note The number of times you can copy a worksheet before you must save the workbook varies with the size of the worksheet.