Macro Help
 

Following is my macro that copies a hidden sheet
(extended reservation) and renames the sheet as the value
in cell C4 on Create Reservation sheet. If C4 is an
existing sheet name the macro returns an error and the new
sheet is named extended reseervatiom(2). I would like, if
there is a duplicate, for extended reservation(2) not to
be created. Also how do I assign short cutkey to the
macro.

Sorry so long and Thanks!!!
'
' create Macro
' Macro recorded 8/21/2003 by zz535a1
'
'

Sheets("EXTENDED RESERVATION").Visible = True
Sheets("EXTENDED RESERVATION").Copy After:=Sheets(2)
Range("C1:C7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B9:B104").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlFormulas,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("EXTENDED RESERVATION").Select
ActiveWindow.SelectedSheets.Visible = False

Call Worksheet_Change


End Sub

Private Sub Worksheet_Change()
Dim sName As String

sName = CStr(Sheets("CREATE RESERVATION").Range
("C4").Formula)
Sheets("EXTENDED RESERVATION (2)").Name = sName

End Sub

..

Macro Help
 

Private Sub Worksheet_Change()
Dim sName As String
Dim ws as worksheet

sName = CStr(Sheets("CREATE RESERVATION").Range
("C4").Formula)

For Each ws In ActiveWorkbook.Worksheets
if ws.Name=sName
exit sub
end if
Next


Sheets("EXTENDED RESERVATION (2)").Name = sName

End Sub

..

-----Original Message-----
Following is my macro that copies a hidden sheet
(extended reservation) and renames the sheet as the value
in cell C4 on Create Reservation sheet. If C4 is an
existing sheet name the macro returns an error and the
new
sheet is named extended reseervatiom(2). I would like,
if
there is a duplicate, for extended reservation(2) not to
be created. Also how do I assign short cutkey to the
macro.

Sorry so long and Thanks!!!
'
' create Macro
' Macro recorded 8/21/2003 by zz535a1
'
'

Sheets("EXTENDED RESERVATION").Visible = True
Sheets("EXTENDED RESERVATION").Copy After:=Sheets(2)
Range("C1:C7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B9:B104").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlFormulas,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("EXTENDED RESERVATION").Select
ActiveWindow.SelectedSheets.Visible = False

Call Worksheet_Change


End Sub

Private Sub Worksheet_Change()
Dim sName As String

sName = CStr(Sheets("CREATE RESERVATION").Range
("C4").Formula)
Sheets("EXTENDED RESERVATION (2)").Name = sName

End Sub

..


.