Copy sheet and prompt for sheet name

[murphyz]

I want to be able to create a copy of an existing sheet and have the user specify the sheet name as it copies, rather than wait until they have copied it and then change the name manually. Ideally, after clicking the command button to copy the name, the user will be prompted to type in the sheet name.

The code I have for creating the copy is as follows (taken straight from a macro), and it's the .Name = "domain.com" section I need to be able to specify.

Private Sub CommandButton1_Click()
Sheets("Template.com").Select
Sheets("Template.com").Copy After:=Sheets(2)
Sheets("Template.com (2)").Select
Sheets("Template.com (2)").Name = "domain.com"
End Sub

Any advice greatly appreciated, thanks.

Mxx

[Stefano Gatto]

Check the InputBox function. Start your sub by collecting in a string
variable the name of the worksheet and Exit from it if Inputbox returned an
empty string; ELSE proceed with the rest of your sub.
--
Stefano Gatto


"murphyz" wrote:

>
> I want to be able to create a copy of an existing sheet and have the
> user specify the sheet name as it copies, rather than wait until they
> have copied it and then change the name manually. Ideally, after
> clicking the command button to copy the name, the user will be prompted
> to type in the sheet name.
>
> The code I have for creating the copy is as follows (taken straight
> from a macro), and it's the .Name = "domain.com" section I need to be
> able to specify.
>
> Private Sub CommandButton1_Click()
> Sheets("Template.com").Select
> Sheets("Template.com").Copy After:=Sheets(2)
> Sheets("Template.com (2)").Select
> Sheets("Template.com (2)").Name = "domain.com"
> End Sub
>
> Any advice greatly appreciated, thanks.
>
> Mxx
>
>
> --
> murphyz
> ------------------------------------------------------------------------
> murphyz's Profile: http://www.excelforum.com/member.php...o&userid=20624
> View this thread: http://www.excelforum.com/showthread...hreadid=484219
>
>

[murphyz]

Thank you, I'm getting there slowly.

I now have it almost working perfectly, although when pressing cancel it ignores my 'you clicked cancel' text and prints out the 'you didn't enter anything' text - am I missing something silly?

mystring = InputBox("Please enter sheet name here")
If mystring = False Then
MsgBox "You clicked cancel"
ElseIf mystring = "" Then
MsgBox "You didn't enter anything"
Else
Sheets("Template.com").Visible = True
Sheets("Template.com").Select
Sheets("Template.com").Copy After:=Sheets(2)
Sheets("Template.com (2)").Select
Sheets("Template.com (2)").Name = mystring
Sheets("Template.com").Select
ActiveWindow.SelectedSheets.Visible = False
End If

Finally, is there a way to copy the sheet to the very end of the tabs - Copy After:=Sheets(2) - regardless of how many worksheets there are?

Many thanks

Mxx

[Stefano Gatto]

Oh sorry, I meant "" by empty string, not FALSE.

So try to replace:

If mystring = False Then
by
If mystring = "" Then

Beware I did not test what I am saying, but am believing it works.

Stefano

--
Stefano Gatto


"murphyz" wrote:

>
> Thank you, I'm getting there slowly.
>
> I now have it almost working perfectly, although when pressing cancel
> it ignores my 'you clicked cancel' text and prints out the 'you didn't
> enter anything' text - am I missing something silly?
>
> mystring = InputBox("Please enter sheet name here")
> If mystring = False Then
> MsgBox "You clicked cancel"
> ElseIf mystring = "" Then
> MsgBox "You didn't enter anything"
> Else
> Sheets("Template.com").Visible = True
> Sheets("Template.com").Select
> Sheets("Template.com").Copy After:=Sheets(2)
> Sheets("Template.com (2)").Select
> Sheets("Template.com (2)").Name = mystring
> Sheets("Template.com").Select
> ActiveWindow.SelectedSheets.Visible = False
> End If
>
> Finally, is there a way to copy the sheet to the very end of the tabs -
> Copy After:=Sheets(2) - regardless of how many worksheets there are?
>
> Many thanks
>
> Mxx
>
>
> --
> murphyz
> ------------------------------------------------------------------------
> murphyz's Profile: http://www.excelforum.com/member.php...o&userid=20624
> View this thread: http://www.excelforum.com/showthread...hreadid=484219
>
>