Afternoon Martin,

Ran the addum macro, and it only runs for a specific number of sheets (44).

Then flags a run time error 1004
Method 'Copy' of object'_Worksheet failed

Do you perhaps have a solution?

Thanks


"Martin Fishlock" wrote:

Have a look at this it needs a bit of tidying up and maybe tweeking.

but it copies the template this is in the current workbook with a name
Template (modify as required).

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 wstemp as worksheet
Dim Rng As Range
Dim ListRng As Range
set wstemp = worksheets("Template") ' this is the one to copy
Set ListRng = Range(Range("B7"), Range("B7").End(xlDown))
For Each Rng In ListRng
If Rng.Text < "" Then
wstemp.copy After:=worksheets(Worksheets.Count)
worksheets(Worksheets.Count).Name = Rng.Text
End If
Next Rng
End Sub

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


"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