One way:

Option Explicit
Sub testme()

Dim TemplWks As Worksheet
Dim NewWks As Worksheet
Dim NewName As String

Application.ScreenUpdating = False

With ThisWorkbook
Set TemplWks = Worksheets("template")

TemplWks.Visible = xlSheetVisible
TemplWks.Copy _
befo=.Sheets(1)
TemplWks.Visible = xlSheetHidden

Set NewWks = .Worksheets(1)

NewWks.Move _
after:=.Sheets(.Sheets.Count)

End With
Application.ScreenUpdating = True

NewName = InputBox(Prompt:="What's the new name, Kenny?")

On Error Resume Next
NewWks.Name = NewName
If Err.Number < 0 Then
MsgBox "That name wasn't valid." & vbLf _
& "Please rename " & NewWks.Name & " yourself."
Err.Clear
End If
On Error GoTo 0

Application.Goto reference:=NewWks.Range("A1"), Scroll:=True

End Sub


SueDot wrote:

I need some Excel 2003 macro help, please.
I have a workbook with two worksheets. The first is a roster of names and
information. The second is an empty template that will use V-Lookup to
retrieve information from the roster based on user inputs.

I want to hide the template worksheet and create a button for the roster
worksheet that has a macro attached to it. The macro needs to copy the
hidden template worksheet and insert it at the end of the workbook. A user
might choose to make one copy, or many. I would also like the macro to
include a pop-up box in the middle of the routine where the user could enter
a Tab name, and then continue on with inserting the newly titled worksheet.
I am unsure of how to invoke the copying of the template worksheet, or if it
is even possible to copy it while it's hidden.

The users are not Excel savvy people. We don't want them to see the
original. From the user's perspective, we want it to be a one-click event to
create a new worksheet, with a simple pop-up to name the tab. From there,
they know how to fill out the template.

Can anyone give me an idea where to start with the code for invoking a copy
of a worksheet, or if it's possible to invoke it while it's hidden?

I hope this makes sense!
Thank you in advance,
Susan
--
Susan

--

Dave Peterson