Thread: Create New WB with copy of Sheets 1 & 2 when sheets count reaches 100

What i want to do is create a New workBook, when the number of worksheets reaches 100.
I have a button set up to create a new record(sheet) from a Template (Sheet1) in a workbook.
To save over sizing and increasing the chances of a file crash, i want to have the WB, if the create a New Record Buton is clicked and there is already 99 Records(sheets) in the file, The make a Copy of the first 2 sheets(Opening Page & Template) and CREATE a NEW WORKBOOK with these in them.

Currently i have this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 25/06/2006 by Corey
'
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Template").Copy Before:=wb.Sheets(1)
For i = wb.Sheets.Count To 2 Step -1
wb.Sheets(i).Delete
Next i
wb.Sheets(1).Name = "Template"

'
End Sub


It will copy 1 Sheet only, But i need to adapt this to copy the first 2 sheets, AND to do so ONLY when the Number of SHEETS totals say 99.



Any idea's, is it possible?

Corey....

Hello Corey,

Sub Macro1()
With ThisWorkbook
If .Worksheets.Count < 99 Then Exit Sub
.Worksheets(Array("Opening Page", "Template")).Copy
End With
End Sub

Sincerely,
Leith Ross

Leith,
Cheers works perfectly thanks


Corey....
"Leith Ross" <Leith.Ross.29ybwn_1151228401.9628@excelforum-nospam.com> wrote
in message news:Leith.Ross.29ybwn_1151228401.9628@excelforum-nospam.com...
>
> Hello Corey,
>
> Sub Macro1()
> With ThisWorkbook
> If .Worksheets.Count < 99 Then Exit Sub
> Worksheets(Array("Opening Page", "Template")).Copy
> End With
> End Sub
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile:
> http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=555333
>

Is it possible to ADAPT this code so that IF less than (<99) sheets are the
case then MACRO2 runs,
If there is 99 sheets then to RUN this Copy Code ?

Sub Macro1()
With ThisWorkbook
If .Worksheets.Count < 99 Then Exit Sub ' Macro2 to run if LESS THAN 99
instead of exit,
BUT
still need EXIT SUB, so creation of a new workbook copy
does
not go ahead when les than 99 sheets.
Worksheets(Array("Enter - Exit Page", "Template")).Copy
End With
End Sub


Think it is asimply step tried:

With ThisWorkbook
If .Worksheets.Count < 99 Then Macro2

Worksheets(Array("Enter-Exit Page", "Template")).Copy
End With
End Sub

But it ALSO creates a COPY, as it does not EXIT the SUB after running
Macro2.


Corey....

Hello Corey,

Here is the revised code...

Sub Macro1()
With ThisWorkbook
If .Worksheets.Count < 99 Then
Call Macro2
Exit Sub
End If
If .Worksheets.Count = 99 Then
.Worksheets(Array("Opening Page", "Template")).Copy
End If
End With
End Sub

Sincerely,
Leith Ross

Thanks heaps Leith.

Perfect....
Just a Q though..
Is it possible to add a step to save the old WorkBook Name as the current
DATE instead, and to rename the NEWLY created WB as the OLD WB's name
somehow ?


Corey....