Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Auto Copy/autofill Text from sheet to sheet if meets criteria | Excel Discussion (Misc queries) | |||
'Copy to' Advance Filter depend only on sheet ID not start sheet | Excel Worksheet Functions | |||
how to find and copy values on sheet 2, based on a list on sheet 1 | Excel Programming | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) |