Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("list")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell

End Sub


Gord Dibben MS Excel MVP

On Tue, 19 Aug 2008 14:11:41 -0700, Noahthek
wrote:

I'm trying to create new worksheets using a Macro.

One of my current worksheets is the layout and formula/data master (we can
call it MasterLayout) and another current worksheet would be where the New
Worksheets are named (MasterName) from an existing list in cells A1:A300; I'd
like each new worksheet to be named from A1, A2, etc.


So that any new worksheets would have the data/layout of MasterLayout and
have the name from MasterName.

How do I do this?

thanks so much for any help,

Noah