Create worksheets from a template

[Brutium]

Hello ALL,

I hope you can help me with this other question I have.

Using a macro, I am able to create worksheets which are named according to the following piece of code:

Sub Addsheets()
Dim LR As Long, i As Long
With Sheets("Sheet2")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = .Range("A" & i).Value
Next i
End With
End Sub


My question is: what do I need to do in order to have a certain template that I created, open when I run the above code?

Any help would be greatly appreciated

 

[shg]

One way:

Sub Addsheets()
    Dim i           As Long
 
    With Worksheets("Sheet2")
        For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
            Worksheets.Add After:=Worksheets(Worksheets.Count), _
                           Type:="C:\myPath\myFile.xlt"    ' or .xls, xlsx, ...
            ActiveSheet.Name = .Cells(i, "A").Text
        Next i
    End With
End Sub

 

[meldoc]

Hello

Try this, You have to change the path and file name, to match your requirements.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Addsheets()<br><SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>LR = .Range("A" & Rows.Count).End(xlUp).Row<br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR<br>Sheets.Add(After:=Worksheets(Worksheets.Count), _<br>  Type:="C:\Users\brian\Desktop\Book1.xlsm").Name = .Range("A" & i).Value<br><SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>