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.


    Thanks in advance…


    Edited once, last by royUK ().

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


  • You need to check if the sheet name exists before running the code.

    Why would you want to copy the activesheet, it might not be the correct one? I would have hidden template sheet to copy.

    Don't use an error handler until you know the code is working correctly.

    Your error handler is all wrong and will run whether an error occurs or not.

  • 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,



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!