Copy Sheet to many sheets at once

S

Sunnyskies

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
 
M

Martin Fishlock

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
 
S

Sunnyskies

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.
 
M

Martin Fishlock

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
 
D

Dave Peterson

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
 
S

Sunnyskies

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
 
S

Sunnyskies

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
 
D

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")
 
S

Sunnyskies

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.
 
D

Dave Peterson

I tried it with 134 names and it worked fine.

What version of excel are you using?


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.
Click to expand...
 
S

Sunnyskies

Morning Dave,

Use windows XP, how many times did you try.

Possible to e-mail to you?

Thanks
 
D

Dave Peterson

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

What version of Excel are you using?
Morning Dave,

Use windows XP, how many times did you try.

Possible to e-mail to you?

Thanks
Click to expand...
 
S

Sunnyskies

Microsoft Office Proffesional Edition 2003

Dave Peterson said:
I don't think that emailing the workbook to me would help you.

What version of Excel are you using?
Click to expand...
 
D

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?
Microsoft Office Proffesional Edition 2003
Click to expand...
 
S

Sunnyskies

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 said:
And I see you've started another thread elsewhere.

I'll bow out.
Click to expand...
 
D

Dave Peterson

You would use it in place of the line that does the copying of the worksheet.
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
Click to expand...
 
Top