VBA- copy template based on tabs in another sheet

[wileymb]

Hey there,

I am so so new to VBA and having some issues that aren't as cut and dry as I had hoped they would be.

Essentially, what I need to do is copy a template to create the same number of worksheets that exist in another workbook. For example, I get a workbook (we'll call it NovemberData.xls) each month with worksheets in it for states (different state data comes in each month). What I would like to do is create a macro that copies a template into a new workbook (call it NovemberReport.xls) and worksheet, and loops to repeat this for each tab in the NovemberData.xls workbook. I would like the worksheet names in NovemberReport.xls to match the worksheet names in NovemberData.xls.

I had previously gotten it to work where I create a copy of the template based on a list, but since the list changes month to month, I thought it would be more efficient to figure out how to do this where it looped through the tabs that were present instead.

Anyone have any ideas? super grateful for any help!

-Mack

[mikeTRON]

Why not make the list dynamic, then you dont have to change the already built VBA?

By that I mean, have a macro that first lists all the tabs, then recretes them in the new workbook? It sounds like you already have the second part done so you just need VBA to help create the list of worksheets right?

[mikeTRON]

Sub ListWorkSheetNames()
'Declare Variables
Dim xWs As Worksheet
'Define Variables
Set xWs = Application.ActiveSheet
'Loop
For i = 1 To Application.Sheets.Count
     xWs.Range("A" & i) = Application.Sheets(i).Name
Next i
End Sub