Macro creates sheet and renames it based on cell value

[notdez]

I have a macro that basically copies a sheet, converts it to values only, and renames the sheet based on a cell value (today's date). I'd like to be able to run the macro multiple times in 1 day, overwriting the older sheet. The macro works once a day but fails when it tries to rename the sheet. Here's the macro:

[pre]

Sub Archive()
'
' Archive Macro
'

'
Sheets("Values (8)").Select
Sheets("Values (8)").Copy After:=Sheets(10)
Range("A1:AG64").Select
Range("A16").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1:B2").Select
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Selection.Delete
Selection.Cut
ActiveSheet.Name = Range("O18").Value
End Sub

[/pre]

 

[xld]

Try deleting the sheet with that name before the rename, wrap it with OnError in case it doesn't exist.