Copying and (re)naming sheets with VBA?

[ExcelDane]

I have a worksheet that I need to copy 54 times to the same workbook. Ultimately, I need to have 11 series of sheets each containing 5 sheets. I propose naming them 1A, 1B, 1C, (...), 11E.

I have two questions:
1. Is there a way to copy and name all these sheets automatically, e.g. using VBA?

2. I tried manually copying the sheet. The column chart contained in the original sheet was not present on the copy. Why is this, and how can I make sure the chart is copied and pasted along with the rest of the sheet? I have not experienced this issue before.

I am using Excel 2007 for Windows Vista.

Thanks in advance for any help.

 

[pboltonchina]

Download and instal ASAP Uilities from http://www.asap-utilties.com and under the Sheets menu, No.2 is Insert Multiple sheets using cell values as sheet names. So make a list of your sheet names, select it all and select the utility. It then asks if you want to use an existing sheet as a template for the new ones, slect your sheet to copy and press OK.

 

[BigLRIP]

pbolton's post looks like spam but it gave me an idea.

Just fill Cells A1:A54 with the names of your new sheets onto Sheet 1. In VBA you could run a For loop to create the new sheets. Keep track of a row index and increase that by 1 on every loop.

For sheetCount = 1 To 54
        strName = Sheets("Sheet1").Cells(row_index, 1).Text
        '''''Create and Name sheet''''''
        row_index = row_index + 1
Next sheetCount

 

[pboltonchina]

I don't post spam, this is a well recognised website.

 

[ExcelDane]

Thanks!
This looks like a useful idea, but I'm still too much of a VBA newbie to totally understand what you mean by "keeping track of a row index". Should I replace "row_index" with something else? How would this macro be able to detect the range where I placed the names for sheets?

pbolton's post looks like spam but it gave me an idea.

Just fill Cells A1:A54 with the names of your new sheets onto Sheet 1. In VBA you could run a For loop to create the new sheets. Keep track of a row index and increase that by 1 on every loop.

For sheetCount = 1 To 54
        strName = Sheets("Sheet1").Cells(row_index, 1).Text
        '''''Create and Name sheet''''''
        row_index = row_index + 1
Next sheetCount

 

[ExcelDane]

I ended up finding my own solutions by modifying a piece of code I found on another site (don't remember the address).
First, I named the sheets using this code:

Sub MasterCopies()
Dim i As Long
Application.ScreenUpdating = False
   For i = 1 To 11
     Sheets("Master").Copy After:=Sheets(Sheets.Count)
     Sheets("Master (2)").Name = Format(i, "0") & "_A"
Next
   For i = 1 To 11
     Sheets("Master").Copy After:=Sheets(Sheets.Count)
     Sheets("Master (2)").Name = Format(i, "0") & "_B"
Next
   For i = 1 To 11
     Sheets("Master").Copy After:=Sheets(Sheets.Count)
     Sheets("Master (2)").Name = Format(i, "0") & "_C"
Next
   For i = 1 To 11
     Sheets("Master").Copy After:=Sheets(Sheets.Count)
     Sheets("Master (2)").Name = Format(i, "0") & "_D"
Next
   For i = 1 To 11
     Sheets("Master").Copy After:=Sheets(Sheets.Count)
     Sheets("Master (2)").Name = Format(i, "0") & "_E"
Next
Application.ScreenUpdating = True
End Sub

Naturally, this particular code is only useful for standardized names like A1, A2 etc.

For sorting the sheets, I just used one of the many examples of code that come up when you google "excel sort sheets".

Problem solved!

 

[BigLRIP]

row_index in my example is just an Integer variable that would start at 1.