Create Worksheets Based on Template

[Monte Sliger]

I have an Excel 2000 workbook which has two worksheets: a template sheet
and a sheet with a list of names in column A. Is there a (relatively)
simple method to create new worksheets based on the template worksheet for
each name in the list of names. For example, if I was doing it manually, I
would copy the template worksheet and rename the copy to the first name in
the list and repeat for each name in the list. This would be very tedious
as the list is fairly long (75 to 100 names). I would like to do this
automatically if possible, expecially as I have to set up several workbooks
like this.

I am fairly familiar with formulas but have only set up simple macros in the
past (such as recording keystrokes for example). If what I need to do
requires a macro, could someone give me a reference to websites or books
which cover how to set up these kinds of macros in a step-by-step process
(i.e. idiot proof).


Thanks for any help.
Monte Sliger

 

[Gord Dibben]

Monte

Assuming your list of names is in column A on Sheet2

Sub Add_NameWS()
'add and name sheets with list in A1:A100 on second sheet
For i = 1 To 100
Worksheets.Add.Name = Worksheets("Sheet2").Cells(i, 1).Value
Next
End Sub

Gord Dibben Excel MVP

 

[Gord Dibben]

Monte

You may want to have a dynamic range rather than the fixed A1:A100

Sub Add_NameWS()
Dim mycount As Long
With Worksheets("Sheet2")
Range(("A1"), Cells(Rows.Count, 1).End(xlUp)).Select
mycount = Selection.Rows.Count
For i = 1 To mycount
Worksheets.Add.Name = Worksheets("Sheet2").Cells(i, 1).Value
Next i
End With
End Sub

Gord

 

[bigwheel]

Try this as a starter:-

Save your template as a .xlt file. In your "list of names" worksheet,
create a macro similar to the following ...

Sub create_newWB()
Do
myFile = ActiveCell
Workbooks.Add Template:="C:\Program Files\MSOffice\Templates\test.xlt"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\" & myFile & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Windows("Names").Activate
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
End Sub

This assumes that the names worksheet is called "Names" and that the active
cell in that file is A1. My test file had A1=JONESA, A2=SMITHB and
A3=WILLIAMSC. After the macro had run, I had opened files names JONESA.XLS,
SMITHB.XLS and WILLIAMSC.XLS

 

[Monte Sliger]

Gord,

Thanks for your reply. The macro you gave works fine for creating blank
worksheets; however, I need to create copies of the worksheet named
"Template" and then rename each of those copies to the names in the list.

Any additional help would be greatly appreciated
Monte Sliger

Monte

You may want to have a dynamic range rather than the fixed A1:A100

Sub Add_NameWS()
Dim mycount As Long
With Worksheets("Sheet2")
Range(("A1"), Cells(Rows.Count, 1).End(xlUp)).Select
mycount = Selection.Rows.Count
For i = 1 To mycount
Worksheets.Add.Name = Worksheets("Sheet2").Cells(i, 1).Value
Next i
End With
End Sub

Gord

Monte

Assuming your list of names is in column A on Sheet2

Sub Add_NameWS()
'add and name sheets with list in A1:A100 on second sheet
For i = 1 To 100
Worksheets.Add.Name = Worksheets("Sheet2").Cells(i, 1).Value
Next
End Sub

Gord Dibben Excel MVP

 

[Monte Sliger]

Bigwheel,

Thanks for your reply. This seems to work to create individual .XLS files
with the names in the list. I need to create copies of the "Template"
worksheet within the same workbook and rename each of those copies to the
names in the list.

Thanks for your help.
Monte Sliger

 

[Dave Peterson]

One way:

Option Explicit
Sub Add_NameWS2()
Dim myCell As Range
With Worksheets("Sheet2")
For Each myCell In .Range(("A1"), _
.Cells(.Rows.Count, 1).End(xlUp)).Cells
Worksheets("template").Copy _
after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = myCell.Value
Next myCell
End With
End Sub

Gord,

Thanks for your reply. The macro you gave works fine for creating blank
worksheets; however, I need to create copies of the worksheet named
"Template" and then rename each of those copies to the names in the list.

Any additional help would be greatly appreciated
Monte Sliger

Monte

You may want to have a dynamic range rather than the fixed A1:A100

Sub Add_NameWS()
Dim mycount As Long
With Worksheets("Sheet2")
Range(("A1"), Cells(Rows.Count, 1).End(xlUp)).Select
mycount = Selection.Rows.Count
For i = 1 To mycount
Worksheets.Add.Name = Worksheets("Sheet2").Cells(i, 1).Value
Next i
End With
End Sub

Gord

 

[Gord Dibben]

Monte

I realized that after posting.

I thought of getting you to save the template sheet as SHEET.XLT in your
XLSTART folder then running this code.

Sub Add_NameWS()
Dim mycount As Long
With Worksheets("Sheet2")
Range(("A1"), Cells(Rows.Count, 1).End(xlUp)).Select
mycount = Selection.Rows.Count
For i = 1 To mycount
Sheets.Add(Type:="Worksheet").Name = Worksheets("Sheet2") _
.Cells(i, 1).Value
Next i
End With
End Sub

BUT.....Dave P. came up with more elegant solution.

Thanks Dave.

Gord

Gord,

Thanks for your reply. The macro you gave works fine for creating blank
worksheets; however, I need to create copies of the worksheet named
"Template" and then rename each of those copies to the names in the list.

Any additional help would be greatly appreciated
Monte Sliger

Monte

You may want to have a dynamic range rather than the fixed A1:A100

Sub Add_NameWS()
Dim mycount As Long
With Worksheets("Sheet2")
Range(("A1"), Cells(Rows.Count, 1).End(xlUp)).Select
mycount = Selection.Rows.Count
For i = 1 To mycount
Worksheets.Add.Name = Worksheets("Sheet2").Cells(i, 1).Value
Next i
End With
End Sub

Gord

 

[Dave Peterson]

You're quite welcome, Gord. <vbg>

Monte

I realized that after posting.

I thought of getting you to save the template sheet as SHEET.XLT in your
XLSTART folder then running this code.

Sub Add_NameWS()
Dim mycount As Long
With Worksheets("Sheet2")
Range(("A1"), Cells(Rows.Count, 1).End(xlUp)).Select
mycount = Selection.Rows.Count
For i = 1 To mycount
Sheets.Add(Type:="Worksheet").Name = Worksheets("Sheet2") _
.Cells(i, 1).Value
Next i
End With
End Sub

BUT.....Dave P. came up with more elegant solution.

Thanks Dave.

Gord

Gord,

Thanks for your reply. The macro you gave works fine for creating blank
worksheets; however, I need to create copies of the worksheet named
"Template" and then rename each of those copies to the names in the list.

Any additional help would be greatly appreciated
Monte Sliger

 

[bigwheel]

See if this will do the trick then. Workbook starts with two sheets,
"Template" and "Names" (No error checking built in, so if duplicate names
exist, the sheet naming will stop)

Sub Macro1()
Sheets("Names").Range("a1").Select
Do
mySheet = ActiveCell
Sheets("Template").Copy Before:=Sheets(1)
Sheets(1).Name = mySheet
Sheets("Names").Select
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
End Sub