My recommendation would be to create a template that you'd use to creat
the number of copies you need and then just save the workbook with th
new worksheets under another name.
The code below is how I've handled copying a master sheet, depending o
a value in a specified worksheet & cell.
Public Sub addSheet()
' This macro will copy a worksheet a variable number of times an
moving
' the new worksheet to the end of the other worksheets in the workbook
Call setLang ' sets the language if necessary
' Set the Reference Number in all worksheets (where applicable)
Call setInfo
' unlock the workbook to allow pages to be added
Call unlockWkBook
' make the worksheet visible
If Sheet1.Visible = xlSheetHidden Then
Sheet1.Visible = xlSheetVisible
End If
' select the page to be copied
Sheet1.Select
' declare the variables, number of pages required plus the numbe
of pages
' existing
addWkSheet = Sheet2.Range("A44").Value
' this variable will take the number generated above and add
worksheet
' to it for the Incident report, the first page is 1, all witnes
reports make
' up the balance of the workbook, therefore its 1+ the number of
' witnesses. If there are other sheets within the workbook you wan
plus
' new copies, the "+1" should be adjusted as necessary
newSheetCount = addWkSheet + 1
' this provides the variable for the final number of worksheets i
the
' workbook and tells the loop when to stop
counter = newSheetCount
' this loop will run until the last worksheet equals the number o
worksheets
' required.
Do Until counter = Worksheets.Count
' this statement indicates which page to be copied and where t
place
' the copied worksheet (last)
Sheet1.Copy After:=Sheets(Worksheets.Count)
' this statement tells the sub to return to the Do Until point an
repeat.
' Loop puts the focus on the first Worksheet (Master)
Sheet1.Select
Sheet1.Visible = xlSheetHidden
' this statement locks the workbook so no further changes can b
made.
Sheets("Workbook(2)").Select
Call lockWkBook
End Su
--
ljsmit
-----------------------------------------------------------------------
ljsmith's Profile:
http://www.excelforum.com/member.php...fo&userid=3053
View this thread:
http://www.excelforum.com/showthread.php?threadid=54668