Morning Dave,

Use windows XP, how many times did you try.

Possible to e-mail to you?

Thanks

"Dave Peterson" wrote:

I tried it with 134 names and it worked fine.

What version of excel are you using?



Sunnyskies wrote:

Afternoon Dave,

I changed the sheet9999 to Parameters as this is where the list of names is,
then ran the macro, which went well, until it hit sheet number 44, then it
popped up the same error message as before : Method 'Copy' of
object_Worksheet' failed.

"Dave Peterson" wrote:

What's the name of the worksheet that contains the list?

I used sheet9999. You'd want to use the correct name.

I also named my template worksheet Template.

You'd want to change this line:
Set TmpWks = Worksheets("Template")



Sunnyskies wrote:

Afternoon Dave,

After copying your macro I hit the following errors.

The command With Worksheet (''sheet9999") tells me out of range. Change to
250 and still does not function. So made it a comment line.
Then made End With also a comment line.
Now battling with the command line
.Copy _
after:=Worksheets(Worksheets.Count)
So altered it a little to .Copy_after:=Worksheets(Worksheet.Count)
Now I get the same run time error as Martins 1004, Method 'Copy' of
object_Worksheet' failed.

Some assistance please.

Thanks

"Dave Peterson" wrote:

Maybe just copying the template (a worksheet in the same workbook???) would be
sufficient:

Option Explicit
Sub testme01()
Dim TmpWks As Worksheet
Dim NewWks As Worksheet
Dim myCell As Range
Dim ListRng As Range

With Worksheets("sheet9999")
Set ListRng = .Range("B7", Range("B7").End(xlDown))
End With
Set TmpWks = Worksheets("Template")

With TmpWks
.Visible = xlSheetVisible
For Each myCell In ListRng.Cells
.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
NewWks.Name = myCell.Value
Next myCell
.Visible = xlSheetHidden
End With
End Sub


Sunnyskies wrote:

All the sheets are on one file.
The names of the other sheets are created by using a macro refering to a list.
This is the macro to create the other sheets and give them a name according
to the list.

Sub NameSheets()
'will add a sheet, and name it
'for each name in column A
'from B7 down till it hits a blank row

Dim Rng As Range
Dim ListRng As Range
Set ListRng = Range(Range("B7"), Range("B7").End(xlDown))
For Each Rng In ListRng
If Rng.Text < "" Then
With Worksheets
.Add(After:=.Item(.Count)).Name = Rng.Text
End With
End If
Next Rng
End Sub

And then I want to copy a template and paste into each of the sheets created.

I hope this will provide some guideance.

"Martin Fishlock" wrote:

Afternoon Sunnyskies:

You will have to give a little more information.

Where are the other sheets?
How do you know the names?
Are they in the same workbook?

Generally you do this:

activesheet.cells.copy worksheets("Sheet1").cells

to copy and paste

but if you know the names you can paste all at once as in

ActiveSheet.Cells.Copy
Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet7", "Sheet9")).Select
Sheets("Sheet2").Activate
Cells.Select
ActiveSheet.Paste


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Sunnyskies" wrote:

Afternoon,

I have a template sheet that I want to copy to many other sheets using a
macro. The other sheets all have unique sheet names.

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson