Maybe you could use something like this (I didn't do anything with the
hiddenness of the worksheet).

Option Explicit
Sub NewSheet_Add()
Dim iCtr As Long

Worksheets("TEMPLATE").Copy Befo=Worksheets(1)

iCtr = 0
With ActiveSheet
On Error Resume Next
Do
iCtr = iCtr + 1
.Name = "Summary - (" & iCtr & ")"
If Err.Number < 0 Then
Err.Clear
Else
Exit Do
End If
Loop
End With

End Sub

StargateFan wrote:

On Sat, 31 Dec 2005 09:39:19 -0600, Dave Peterson
wrote:

How about just naming your template worksheet "Summary".

<sigh I know ... pickie, pickie. It's because all my worksheets of
this type have the main hidden sheet with the name TEMPLATE. And I've
gotten to know my users. One has to keep it simple, stupid. It makes
it easier on the user and on me to have a standard yet easily
identifiable name. (As you know, if there's a way to mess things up
or not understand, our newbie users will find a way ... <vbg).

This is XL2K, though! Surely there's way around this? It does some
wonderful and lovely things <g. Is there no way to code this so that
I have TEMPLATE as hidden but it will work when user has to make more
than one copy at any given moment??

Thanks. :oD

StargateFan wrote:

On Sat, 31 Dec 2005 10:03:24 -0000, "Norman Jones"
wrote:

Hi StargateFan,

Try:

'=============
Sub NewSheet_Add()
Worksheets("TEMPLATE").Copy Befo=Worksheets(1)
With ActiveSheet
.Name = Replace(.Name, "TEMPLATE", "Summary")
End With
Worksheets(1).Visible = xlSheetVisible
End Sub
'<<=============

This came really, really close.

See, when I click on the original code and create any number of
sheets, I get TEMPLATE (2), TEMPLATE (3), TEMPLATE (4) ... .

The above modified code made one copy just fine with name "Summary
(2)", but the 2nd time I clicked the button, I got an error. 3rd
button click gave me TEMPLATE (2); 4th gives me error, 5th click gave
TEMPLATE (3) <g.

Is it possible to just get a "Summary (2), Summary (3)" right off the
bat? If not, will just go back to the original code, but it would be
a way to help the users to have the name partially filled in to which
they customize.

(I have XL2K. Sorry, forgot to say. Sometimes that makes a
difference, of course.)

Thanks! :oD

---
Regards,
Norman



"StargateFan" wrote in message
.. .
On Fri, 30 Dec 2005 18:15:45 -0600, "Chip Pearson"
wrote:

Try

Worksheets.Add.Name = "Summary"

What a neat idea.

What if we have code like this that copies a worksheet rather than
just adds a blank new one:

Sub NewSheet_Add()
Worksheets("TEMPLATE").Copy Befo=Worksheets(1)
Worksheets(1).Visible = xlSheetVisible
End Sub



I get sheets named TEMPLATE (2), TEMPLATE (3), TEMPLATE (4), etc., as
the name of the sheet being copied is called TEMPLATE.

Is there a way to get the code to name the sheets to, say, "Summary -
" as well? Having them come out as Summary - (2), Summary - (3),
Summary - (4), ..., would be just fine. The user would customize by
adding additional date info needed on the tab name - this would just
save them that time and remembering naming format.

Thanks! :oD

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"dogpigfish" wrote in
message
...
What code can I use to create a new sheet and call it
"Summary".




--

Dave Peterson

--

Dave Peterson