If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
quick copy worksheet into other sheets in same workbook
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 |
#2
|
|||
|
|||
quick copy worksheet into other sheets in same workbook
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 M" wrote: 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 |
#3
|
|||
|
|||
quick copy worksheet into other sheets in same workbook
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 On Tue, 20 Dec 2005 12:59:02 -0800, Mick M Mick wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Protect Workbook vs Worksheet?? | Dan B | Worksheet Functions | 3 | November 7th, 2005 09:02 PM |
copy data from one worksheet to identical sheet in different workbook | akid12 | General Discussion | 2 | July 6th, 2005 02:55 AM |
copy COLUMN from 1 worksheet to another (in a different workbook) | DavidB | General Discussion | 3 | January 15th, 2005 02:47 PM |
Copy formulae into another workbook and eliminate worksheet refer. | Jimbo | General Discussion | 2 | October 13th, 2004 04:54 PM |
Copy sheet from one workbook to a new workbook without having them linked | Gilbert Aponte | General Discussion | 1 | June 18th, 2004 09:46 PM |