On Aug 21, 6:25 pm, "Zone" wrote:
Sean, change it like this and add the new function as shown. James

Sub CopySht()
Dim shtName As String, newShtName As String
shtName = ActiveSheet.Name
newShtName = Format([a5], "dd-mm-yy")
If Not SheetExists(newShtName) Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
Sheets(shtName).Activate
Else
MsgBox "You have already created this week.", vbCritical
End If
End Sub

Private Function SheetExists(sname) As Boolean
'from John Walkenbach
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function



James can I test your knowledge again? If an attempt was made to
create a sheet with the same name / date (which I can't have). How in
this instance if it happened could I cancel out of the macro with a
message box saying "You have already created this week"?

Thanks- Hide quoted text -

- Show quoted text -

James, I'm getting a debug on line "If Not SheetExists(newShtName)
Then", saying sub or function not defined