make multiple copies of a sheet in a new workbook



  • 1. Formula help in Excel 2007
    I know it's possible to link cells from one worksheet to another but not sure if what I want to achieve is possible (I use only the basic functions in Excel). I want to insert information into one sheet and have a version of that information appear in another sheet. For example, I have a list of jobs I've been commissioned to do in sheet 1 and they are in a list in date order, how much I got paid and what the job was. In another sheet I have a list of people who've commissioned me to do work and what the job was - so I can see at a glance who is offering me the most work. Some of the information is repeated e.g. the job, the date etc. At the moment I am manually typing in the same information in each sheet. Can I link cells so that any info repated autmatically appears in the second sheet? -- Millie
  • 2. year - 1
    Hi Is there a way in a worksheet - Functions where i can take a cell value (date) and make it a year less like 01-11-2009 (danish date) shall be 01-11-2008 I can in VB, but can't find it in a waotksheet alvin
  • 3. I'd like to change my column markers from numbers to letters
    Worksheet columns are labeled with numbers, making formulas very difficult to follow (=R[-5]C*R[-6]C[11]) instead of (B13*M7). Thanks for help.
  • 4. Macro:- Find first blank cell and write text
    Hi All, I have a spreadsheet in 9 Columns (A to I) and, at the moment, 772 Rows. I want to write a macro that:- Finds the first blank cell in column B, (ie B772) Writes some text into that cell, Steps to the next cell on the right, (ie C772) Writes some text into that cell, Steps to the next cell on the right, (ie D772) Writes some text into that cell, Steps across 2 cells to the right, (ie F772) Writes some text into that cell, Steps across 3 cells to the right and 1 down, (ie I773) Writes some text into that cell. End the macro. The text being written to the cells will be the same on all occasions. Can anyone help pleaese, many thanks. -- Mac Macdonald

make multiple copies of a sheet in a new workbook

Postby Z2FsZWFubmU » Sun, 13 Feb 2005 00:27:05 GMT

I am using excel 2003 and I need to make 29 copies of  one sheet form a 
workbook to another all at once.  Does anyone know of a way to do that?

Re: make multiple copies of a sheet in a new workbook

Postby Gord Dibben » Sun, 13 Feb 2005 02:35:05 GMT


Select the sheet then right-click on tab and "Move or Copy"

Check the "create a copy" button and in "to book:" select New Book.

Now you have one copy in a new book.

To make 28 more run this macro.

Sub SheetCopy22()
Dim i As Long
Application.ScreenUpdating = False
howmany = InputBox("Copy Active Sheet How Many Times?")
For i = 1 To howmany
    ActiveSheet.Copy Before:=Sheets(1)
    Next i
Application.ScreenUpdating = True
End Sub

Gord Dibben   Excel MVP

On Fri, 11 Feb 2005 07:27:05 -0800, "galeanne"

Re: make multiple copies of a sheet in a new workbook

Postby JulieD » Mon, 14 Feb 2005 02:21:01 GMT


not quite all at once without using code ..
but one way to get to what you want reasonably fast would be:
with the workbook that you want to copy the sheet from open (let's call it 
book 1) choose tools / options / general and set sheets in new workbook to 
now choose file / new and open a new workbook with the 28 sheets (book 2) - 
go back to  tools / options / general and set sheets in new workbook to 3 
(or whatever you want)

now from the window menu switch back to book 1
right mouse click on the sheet that you want to copy to the new book
move or copy sheet
tick the
create a copy check box (v.important)
from the drop down list choose Book 2
choose to copy it before all other sheets
click OK

- you will be automatically switched to book 2 with the sheet copied in.
now you need to select all the sheets in the workbook - the "copied in" 
sheet should be in front of you and on the left of all other sheet tabs, 
click on its sheet tab and then click on the little arrow (bottom left of 
screen) that takes you to the last worksheet - hold down the shift key & 
click on the last worksheet
you should see all the sheet tabs turn white (let the shift key go)
now select all the cells on the sheet that you can see (should still be the 
"copied in" sheet)
then choose from the menu
edit / fill / across worksheets
and you should now have 29 copies of the same thing,

Don't forget to ungroup the sheets, by right mouse clicking on a sheet tab 
and choosing ungroup sheets.


Re: make multiple copies of a sheet in a new workbook

Postby Gord Dibben » Mon, 14 Feb 2005 04:59:56 GMT

Sub DupSheet()
    Dim Counter As Integer
    Application.ScreenUpdating = False
    For Counter = 1 To 28
        ActiveSheet.Copy , Worksheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Name = "Sheet" & Counter + 1 ''Modify as needed
End Sub

Gord Dibben Excel MVP

On Sun, 13 Feb 2005 01:21:01 +0800, "JulieD" < XXXX@XXXXX.COM >

Similar Threads:

1.Copying multiple sheets to new Workbook

Is it possible to "split" mutliple worksheets into mutiple workbooks in a 
single function?

For example, if there are 15 worksheets in one workbook that I would like to 
"split" into 15 individual workbooks, is it possible to move them all at once 
as opposed to performing an individual copy to new workbook for each tab?

Any help is appreciated,

2.Copying multiple sheets into a NEW workbook

3.Copy fixed range of multiple sheets into new workbook

I have a workbook with multiple sheets in them. I want to copy a
particular fixed range of each sheet, in which the sheetname ends on
PY) into a new workbook. For example sheetname= Emuls(actual vs PY)
range b4:s40.

Each range has to be copied into a (seperate) new sheet with the
sheetname it had on the original workbook.

I have no clou how to start the procedure????

thankx in advance

4.copy selected tabs from multiple workbooks to a new workbook

I want to compare a user text input to the name of a worksheet tab(A or B), 
and if the user input matches A or B copy the corresponding tab to a new 
workbook, in order to combine selections from 8 sources to one workbook 

5.Change link-source on file after copy the sheet into new a new workbook


I like to have a macro that made a copy of the actual sheet into a new
workbook (create a copy -> new book).
Then the macro has to save directly the file into the directory C:/
Apps with the name "Template-date-time.xls" (date and time as actual).
After that the action should be taken: menuaction "Edit -> Links ->
Change Source" and then change the source to the before saved

Why this action....  because my file has buttons on it. When I copy
the sheet with the buttons on it, it will get "links" to the original
file because of the VBA codes on it.

Possible ??  or another solution ?

greetings from John

6. Copy sheet from one workbook to a new workbook without having them linked

7. copy sheets in workbook to new workbook

8. Copy Range to a New WorkBook + Name Sheet a cell Value + Name WorkBook another Celll Value

Return to MS EXCEL


Who is online

Users browsing this forum: No registered users and 66 guest