Code to Create a new WorkSheet

[Corey]

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....

 

[Tom Ogilvy]

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

 

[Corey]

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]

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

 

[Corey]

Thanks again Tom.
Worked like a gem.

Corey....

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