quick copy worksheet into other sheets in same workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am wanting to copy a worksheet that is set up to show student results,
targets and achievements into approximately 150 other worksheets in the same
workbook. Is there a quick way of doing this other than copying and pasting?
I want formats, colours and column widths etc to stay the same. In other
words I want to produce about 150 identical copies of my layout as quickly as
possible with the minimum of effort.
I am using Excel 2003.
Thanks in advance for any help and advice offered
 
Right-click on the worksheet tab and select "Move or Copy", then use the
dialog box that appears to specify where you want to paste the worksheet
copy. Be sure to check the box that says "Create a copy". If you have data in
the worksheet that you don't want to have in the other worksheets, make one
copy of it, delete the data from the copy, and then use the copy to create
the other 149 worksheets.

GwenH
Master MOS
 
Mick

What do you want to name these sheets?

There are several variations of code to copy one worksheet many times but
usually these 150 copies each get a name.

Here's code from Dave Peterson that assumes you have a list of names in
Column A on a sheet named "list".

Say "Student_1" through "Student_150" in A1:A150

The sheet to be copied is named "Template"

Sub testme01()
'Dave Peterson
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 Excel MVP
 
Back
Top