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

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

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

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

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

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

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

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

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

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

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

I don't think that emailing the workbook to me would help you.

What version of Excel are you using?

Sunnyskies wrote:

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


--

Dave Peterson

Microsoft Office Proffesional Edition 2003

"Dave Peterson" wrote:

I don't think that emailing the workbook to me would help you.

What version of Excel are you using?

Sunnyskies wrote:

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


--

Dave Peterson

I use xl2003 with windows XP (home), too.

I don't have a guess why it doesn't work for you.

Maybe it's time to split your data into smaller pieces and run it multiple
times?

Sunnyskies wrote:

Microsoft Office Proffesional Edition 2003

"Dave Peterson" wrote:

I don't think that emailing the workbook to me would help you.

What version of Excel are you using?

Sunnyskies wrote:

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


--

Dave Peterson


--

Dave Peterson

And I see you've started another thread elsewhere.

I'll bow out.

Dave Peterson wrote:

I use xl2003 with windows XP (home), too.

I don't have a guess why it doesn't work for you.

Maybe it's time to split your data into smaller pieces and run it multiple
times?

Sunnyskies wrote:

Microsoft Office Proffesional Edition 2003

"Dave Peterson" wrote:

I don't think that emailing the workbook to me would help you.

What version of Excel are you using?

Sunnyskies wrote:

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


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Morning Dave,

I thought that broading my cry for help would provide more possible solutions.

I see that on http"//mcse.ms/message2392323.html you corrected a command line
MyWorkbook.Sheets.Add Type:="c:\path\templatefilename.xlt"

Where would I put this line on my macro?

Thanks

"Dave Peterson" wrote:

And I see you've started another thread elsewhere.

I'll bow out.

Dave Peterson wrote:

I use xl2003 with windows XP (home), too.

I don't have a guess why it doesn't work for you.

Maybe it's time to split your data into smaller pieces and run it multiple
times?

Sunnyskies wrote:

Microsoft Office Proffesional Edition 2003

"Dave Peterson" wrote:

I don't think that emailing the workbook to me would help you.

What version of Excel are you using?

Sunnyskies wrote:

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


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

You would use it in place of the line that does the copying of the worksheet.

Sunnyskies wrote:

Morning Dave,

I thought that broading my cry for help would provide more possible solutions.

I see that on http"//mcse.ms/message2392323.html you corrected a command line
MyWorkbook.Sheets.Add Type:="c:\path\templatefilename.xlt"

Where would I put this line on my macro?

Thanks

"Dave Peterson" wrote:

And I see you've started another thread elsewhere.

I'll bow out.

Dave Peterson wrote:

I use xl2003 with windows XP (home), too.

I don't have a guess why it doesn't work for you.

Maybe it's time to split your data into smaller pieces and run it multiple
times?

Sunnyskies wrote:

Microsoft Office Proffesional Edition 2003

"Dave Peterson" wrote:

I don't think that emailing the workbook to me would help you.

What version of Excel are you using?

Sunnyskies wrote:

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


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson