New Worksheets Macro

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
<Noahthek.TakeThisOut@discussions.microsoft.com> 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

My question is, why do you need 300 worksheets?
Perhaps one would do with filtering, etc.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1.TakeThisOut@austin.rr.com
"Noahthek" <Noahthek.TakeThisOut@discussions.microsoft.com> wrote in message
news:42B1C584-1F1B-45AF-9E5F-D8AA95FD2CC3@microsoft.com...
> 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

Thank you very much.

It worked reasonably well the first time, stopping after about 50 or 60
worksheets. But then it stopped and no matter how many times I try to
recreate it I get the same error.

This is the section that causes the problem:

TemplateWks.Copy After:=Worksheets(Worksheets.Count)

I receive a run time error 1004; Method 'Copy' of Object'_Worksheet' failed

Maybe there's something I'm missing. I'm relatively new to coding although I
do create simple macros from time to time.

To answer the person who asked if filtering would be an option: Sadly, no.
This report is tracking hours worked on specific projects and each project
needs a separate worksheet. There are about 500 of them.

Thanks,
Noah

Below is the full code:

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

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

Set TemplateWks = Worksheets("MasterMatter")
Set ListWks = Worksheets("MasterAttorney")
With ListWks
Set ListRng = .Range("a7:a478", .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

I just tested and the macro copied Template sheet 607 times based on a list
of names in A1:A607 on the sheet named List.

Perhaps you have a duplicate name in your source?


Gord


On Wed, 20 Aug 2008 08:41:01 -0700, Noahthek
<Noahthek DeleteThis @discussions.microsoft.com> wrote:

>Thank you very much.
>
>It worked reasonably well the first time, stopping after about 50 or 60
>worksheets. But then it stopped and no matter how many times I try to
>recreate it I get the same error.
>
>This is the section that causes the problem:
>
>TemplateWks.Copy After:=Worksheets(Worksheets.Count)
>
>I receive a run time error 1004; Method 'Copy' of Object'_Worksheet' failed
>
>Maybe there's something I'm missing. I'm relatively new to coding although I
>do create simple macros from time to time.
>
>To answer the person who asked if filtering would be an option: Sadly, no.
>This report is tracking hours worked on specific projects and each project
>needs a separate worksheet. There are about 500 of them.
>
>Thanks,
>Noah
>
>Below is the full code:
>
>Sub CreateNameSheets()
>' by Dave Peterson
>' List sheetnames required in col A in a sheet: MasterAttorney
>' Sub will copy sheets based on the sheet named as: MasterMatter
>' and name the sheets accordingly
>
>Dim TemplateWks As Worksheet
>Dim ListWks As Worksheet
>Dim ListRng As Range
>Dim myCell As Range
>
>Set TemplateWks = Worksheets("MasterMatter")
>Set ListWks = Worksheets("MasterAttorney")
>With ListWks
>Set ListRng = .Range("a7:a478", .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
>