Code to Create a new WorkSheet
 

Is there code to create a new WorkSheet in a WorkBook and have it a
duplicate of another WorkSheet ?
Is there a limit to how many WorkSheets can be in a WorkBook ? Over time
there may be 10,000 sheets needed.

I have a costing sheet that i want to set up, but want to be able to search
through all records to find specific values in a costing sheet.
Is there a formula to search through ALL WorkSheets in the selected cell for
values that match, and to ONLY display those WorkSheets ?
I want to place a Button on the 1st WorkSheet and IF clicked, it creates a
New WorkSheet COPY of the Costing Sheet template.

How would i go about this?


Corey....

Code to Create a new WorkSheet
 

It is limited by memory.

Putting 10000 sheets in a workbook would not be very smart in my opinion.
Excel workbooks can get corrupted and then all your data could be lost.
Limit it to some reasonable number (less than 100 I would think) and have
multiple workbooks. Make backup copies of each.

With Activeworkbook
.worksheets("Sheet1").Copy After:=.Worksheets(.Worksheets.count)
End With

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...

Is there code to create a new WorkSheet in a WorkBook and have it a
duplicate of another WorkSheet ?
Is there a limit to how many WorkSheets can be in a WorkBook ? Over time
there may be 10,000 sheets needed.

I have a costing sheet that i want to set up, but want to be able to
search
through all records to find specific values in a costing sheet.
Is there a formula to search through ALL WorkSheets in the selected cell
for
values that match, and to ONLY display those WorkSheets ?
I want to place a Button on the 1st WorkSheet and IF clicked, it creates a
New WorkSheet COPY of the Costing Sheet template.

How would i go about this?


Corey....

Code to Create a new WorkSheet
 

Thanks for the reply Tom.
Also thatks for the advice on the file size.

Is there a step i can add to this to Re-Name the Sheet a value from a Msg
Box, Instaed of (Sheet1.2.3.4.5 ets) ?

Corey....
"Tom Ogilvy" wrote in message
...
It is limited by memory.

Putting 10000 sheets in a workbook would not be very smart in my opinion.
Excel workbooks can get corrupted and then all your data could be lost.
Limit it to some reasonable number (less than 100 I would think) and have
multiple workbooks. Make backup copies of each.

With Activeworkbook
.worksheets("Sheet1").Copy After:=.Worksheets(.Worksheets.count)
End With

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...

Is there code to create a new WorkSheet in a WorkBook and have it a
duplicate of another WorkSheet ?
Is there a limit to how many WorkSheets can be in a WorkBook ? Over time
there may be 10,000 sheets needed.

I have a costing sheet that i want to set up, but want to be able to
search
through all records to find specific values in a costing sheet.
Is there a formula to search through ALL WorkSheets in the selected cell
for
values that match, and to ONLY display those WorkSheets ?
I want to place a Button on the 1st WorkSheet and IF clicked, it creates
a
New WorkSheet COPY of the Costing Sheet template.

How would i go about this?


Corey....

Code to Create a new WorkSheet
 

Dim sh as Worksheet
Dim msg as String, sName as String
msg ="Enter a sheet name (no special characters)"
do
sname = InputBox(msg)
if sname = "" then exit sub
On error resume Next
set sh = worksheets(sName)
on error goto 0
msg = "Name already exists, try again: "
loop while not sh is nothing
With Activeworkbook
.worksheets("Sheet1").Copy After:=.Worksheets(.Worksheets.count)
End With
Activesheet.Name = fname

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...


Thanks for the reply Tom.
Also thatks for the advice on the file size.

Is there a step i can add to this to Re-Name the Sheet a value from a Msg
Box, Instaed of (Sheet1.2.3.4.5 ets) ?

Corey....
"Tom Ogilvy" wrote in message
...
It is limited by memory.

Putting 10000 sheets in a workbook would not be very smart in my
opinion.
Excel workbooks can get corrupted and then all your data could be lost.
Limit it to some reasonable number (less than 100 I would think) and
have
multiple workbooks. Make backup copies of each.

With Activeworkbook
.worksheets("Sheet1").Copy After:=.Worksheets(.Worksheets.count)
End With

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...

Is there code to create a new WorkSheet in a WorkBook and have it a
duplicate of another WorkSheet ?
Is there a limit to how many WorkSheets can be in a WorkBook ? Over
time
there may be 10,000 sheets needed.

I have a costing sheet that i want to set up, but want to be able to
search
through all records to find specific values in a costing sheet.
Is there a formula to search through ALL WorkSheets in the selected
cell
for
values that match, and to ONLY display those WorkSheets ?
I want to place a Button on the 1st WorkSheet and IF clicked, it
creates
a
New WorkSheet COPY of the Costing Sheet template.

How would i go about this?


Corey....

Code to Create a new WorkSheet
 

Thanks again Tom.
Worked like a gem.

Corey....
"Tom Ogilvy" wrote in message
...

Dim sh as Worksheet
Dim msg as String, sName as String
msg ="Enter a sheet name (no special characters)"
do
sname = InputBox(msg)
if sname = "" then exit sub
On error resume Next
set sh = worksheets(sName)
on error goto 0
msg = "Name already exists, try again: "
loop while not sh is nothing
With Activeworkbook
.worksheets("Sheet1").Copy After:=.Worksheets(.Worksheets.count)
End With
Activesheet.Name = sName

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...


Thanks for the reply Tom.
Also thatks for the advice on the file size.

Is there a step i can add to this to Re-Name the Sheet a value from a Msg
Box, Instaed of (Sheet1.2.3.4.5 ets) ?

Corey....
"Tom Ogilvy" wrote in message
...
It is limited by memory.

Putting 10000 sheets in a workbook would not be very smart in my
opinion.
Excel workbooks can get corrupted and then all your data could be lost.
Limit it to some reasonable number (less than 100 I would think) and
have
multiple workbooks. Make backup copies of each.

With Activeworkbook
.worksheets("Sheet1").Copy After:=.Worksheets(.Worksheets.count)
End With

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...

Is there code to create a new WorkSheet in a WorkBook and have it a
duplicate of another WorkSheet ?
Is there a limit to how many WorkSheets can be in a WorkBook ? Over
time
there may be 10,000 sheets needed.

I have a costing sheet that i want to set up, but want to be able to
search
through all records to find specific values in a costing sheet.
Is there a formula to search through ALL WorkSheets in the selected
cell
for
values that match, and to ONLY display those WorkSheets ?
I want to place a Button on the 1st WorkSheet and IF clicked, it
creates
a
New WorkSheet COPY of the Costing Sheet template.

How would i go about this?


Corey....