Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate worksheet question
Similar to a question from yesterday, but a new twist.
I have the following code which upon clicking the OK command button, the user is asked in a message box to enter a name for a new worksheet. The active worksheet is then copied to this new sheet and it is saved in the same workbook. Private Sub cmdOK_Click() Do Dim nSheet As Worksheet Dim NameBox As String NameBox = Application.InputBox("Please type a name for the new worksheet", "Creating New Sheet", , , , , , 2) If NameBox = "" Or NameBox = "False" Then MsgBox "Please type a name for the new worksheet" End If Loop Until Not NameBox = "" Or NameBox = "False" Sheets("STD Calc").Copy Befo=Sheets(2) Set nSheet = ActiveSheet nSheet.Name = NameBox Unload Me End Sub How can I tweak this code so that it doesn't copy to a brand new worksheet as named by the user, rather it copies to an existing worksheet within this same workbook? The user would just need to name which worksheet it is copied to. Thanks for any help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate worksheet question
To make it easiest for the user, I would create a form with a list bo and a command button. Then, you could load the list box with the shee names and the user could select from the list. Something like this: Private Sub Userform_Initialize() Dim intsheets As Integer ListBox1.Clear intsheets = 2 Do While intsheets < (Sheets.Count + 1) ListBox1.AddItem Worksheets(intsheets).Name intsheets = intsheets + 1 Loop End Sub Private Sub cmdOK_Click() Dim nSheet As Worksheet Dim NameBox As String Namebox = Listbox1.Value Application.DisplayAlerts = False Worksheets(Namebox).Delete Application.DisplayAlerts = True Sheets("STD Calc").Copy Befo=Sheets(2) Set nSheet = ActiveSheet nSheet.Name = NameBox Unload Me End Sub This will delete the named sheet and then copy the specified sheet renaming it to the old sheet -- kev_0 ----------------------------------------------------------------------- kev_06's Profile: http://www.excelforum.com/member.php...fo&userid=3504 View this thread: http://www.excelforum.com/showthread.php?threadid=56578 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to delete a duplicate Excel worksheet within that worksheet? | Excel Discussion (Misc queries) | |||
Delete Duplicate Rows Macro Question | Excel Discussion (Misc queries) | |||
Challenging Duplicate Question... | Excel Worksheet Functions | |||
Another question on how to find duplicate data | Excel Worksheet Functions | |||
Newbie question on deleting duplicate rows | Excel Programming |