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