Duplicate worksheet question

[Guest]

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 Before:=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!

 

[kev_06]

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 Before:=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