SImple Excel Problem

[twosides]

Hi Folks,
I have a spreadsheet that is hidden from the user (its a blank timesheet master basically). I have another sheet that is not blank called 'instructions', this simply tells the user what to type and where to type.
The hidden sheet is called say "master".
All i want to do is put a simple command button on the 'instruction' sheet that will copy the hidden sheet called "master" and present the user with a dialog to get them to type in the new sheet name ie Dec, Jan or Feb etc. Hit return or ok . And then you simply have a new sheet with a name of their choice, whist keeping the master hidden from danger etc.
Thanx

[carln]

I found this link that might be of some help.

http://www.mvps.org/dmcritchie/excel/sheets.htm

Cheers,
Carl

[DKenny]

Welcome to the Forums!

The following should help.

Sub CopySheet()
Dim MySheet As Worksheet
Dim SheetName As String
 
    'Get The new sheet name from the user
    Do While SheetName = ""
        SheetName = InputBox("Please Enter New Sheet Name", "New Sheet")
    Loop
    
    With ThisWorkbook
        'Copy the master sheet to the end of the workbook
        .Worksheets("Master").Copy After:=.Worksheets(.Worksheets.Count)
        'Rename the last sheet in the workbook
        .Worksheets(.Worksheets.Count).Name = SheetName
        'Make the new sheet visible
        .Worksheets(.Worksheets.Count).Visible = True
    End With
End Sub

[twosides]

Thanks Carl and Declan for your replies.
I tried yours Declan as it was easier form the start.
It worked a treat but after a few times it came up with a Microsoft Visual Basic window that said:
Run-Time error '9'
Subscript out of range
With the 'end' and 'debug' buttons at the bottom of the dialog window.

I manually did an unhide on the hidden sheet and found that the name had been left as 'Sheet (2)' presumably as the code had failed half way through and not renamed it back to 'master'.

I also noticed that if i tried it several times after renaming the sheet back to 'master' and hiding it again the same thing would eventually happen.

BUT. If i did not hide the sheet named 'master' and left it visible the routine (so far has worked with no problems.

Any ideas.

Thanx