Macro to Copy hidden worksheet to new worksheet

[SueDot]

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

 

[Dave Peterson]

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 _
before:=.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

 

[JLGWhiz]

Create a button from the Control Toolbox. In design mode,
right click the button and select "View Code" from the pop
up menu. Copy the code below into the code window. In the
properties window you can put a name on the switch and set
the back color, size it, etc.

Private Sub CommandButton1_Click()
Dim sh as Worksheet
Set Tsh = Sheets("Template") 'change to actual name
Tsh.Visible = True
Tsh.Copy After:=Sheets(Sheets.Count)
shName = InputBox("ENTER A NAME FOR A SHEET", "NEW SHEET NAME")
ActiveSheet.Name = shName
Tsh.Visible = False
End Sub

 

[JLGWhiz]

Forgot to change the Dim statement. Use this code instead.

Private Sub CommandButton1_Click()
Dim Tsh as Worksheet
Set Tsh = Sheets("Template") 'change to actual name
Tsh.Visible = True
Tsh.Copy After:=Sheets(Sheets.Count)
shName = InputBox("ENTER A NAME FOR A SHEET", "NEW SHEET NAME")
ActiveSheet.Name = shName
Tsh.Visible = False
End Sub

 

[SueDot]

Thanks to both of you - you were of great help!!!