Need assistance with VBA Code

Hello All,

I have the below simple vba code assigned to a button on my workbook, it’s to create a copy of the active sheet on a monthly basis, but I also have the workbook structure disabled and protected after the code is run. What I need assistance with is, adding code to the existing code preventing it from creating duplicates if the button is clicked in error more than once.

VBA CODE:

Sub CopyMyWorkSheet()


ThisWorkbook.Unprotected “Password”


On Error GoTo Err_MyWorkSheet
Dim ws As Worksheet
Set ws = ActiveSheet
Dim wsc As Worksheet
ws.Copy , ws
Set wsc = ThisWorkbook.Worksheets(ws.Index + 1)
wsc.Name = “MyWorkSheet “ & Format(DateAdd(“m”, 1, ws.[E3].Value), “mm-did-yy”)


ThisWorkbook.Protect”Password”


Exit_MyWorkSheet:
Exit Sub
Err_MyWorkSheet:
MsgBox “Error “ & Err.Number & “: “ & Err.Description


ThisWorkbook.Protect”Password”


Resume Exit_MyWorkSheet
End Sub

Thanks in advance…

Cheers!

Reason I want to copy the active sheet is because these are invoices (monthly/quarterly)with running totals and the active sheet would be the latest and once I copy it the ending balance would be my beginning balance of the new copy and I need to keep historical/tangible copies. And you’re correct of the error handler just haven’t had time to revisit to correct it. Thank you so much this looks great. Just one question, I’m not familiar with functions do I need to add a button and assign function and run it?

Hello royUK,

Greatly appreciate the assistance,

FYI - I got Run-time error 424; Object required on the below code line.

“If not WksExists(wsc.Name) Then”

Thanks again,

Cheers,

gdraco123