Copy sheet and prompt for sheet name
 

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

Copy sheet and prompt for sheet name
 

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

Copy sheet and prompt for sheet name
 

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

Copy sheet and prompt for sheet name
 

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