Excel userform vba add and save worksheet

  • Hello. I'm not exactly an expert in Excel VB by any means, but any assistance would be welcome. I really don't know how to go about this part.

    I have created a userform, from which data relating to taxes is produced and stored on a worksheet named "Data" for fiscal quarters 1 to 4. My idea is that the data in every quarter can be archived in separate worksheets and saved into another workbook for that specific fiscal year, then at the end of that year another workbook can be created for which that fiscal years fiscal quarters worksheets can be archived and so on.

    I've created some code for the archive function, but i don't know how to add on to it. Most of the code was was from other web sites and threads.


    Below is what i have got so far.

                     


    Any assistance will be greatly appreciated.

  • It's much more efficient to keep the data in one workbook, then build a report to provide data for a chosen quarter. It's also more secure, especially with tax data, to maintain one workbbook, there's less chance of missing changes made in the master workbook.


    Provide an example workbook with some dummy data and I'll take a look.

    1. I've just looked through your code and it needs a lot of tidying up.
    2. You appear to use PasteSpecial without copying anything.

    If you really want to create a new workbook then it looks like you are simply copying the completed data to another workbook. Are there formulas in the data to be removed?


    1. I would simply copy that data sheet to a new workbook
    2. Rename the workbook and save it.
    3. Next, clear the data from the existing sheet.

    The above way would reduce the amount of code and be more efficient

    1. I've just looked through your code and it needs a lot of tidying up.
    2. You appear to use PasteSpecial without copying anything.

    If you really want to create a new workbook then it looks like you are simply copying the completed data to another workbook.


    1. I would simply copy that data sheet to a new workbook
      Rename the workbook and save it.
    2. Next, clear the data from the existing sheet.

    Are there formulas in the data to be removed?

  • This is my attempt to tidy up your existing code with comments


  • Hello RoyUK.

    Thankyou for your quick response.

    I have attached my program for you to look over. My step brother asked me if i could help him out and thus far, well you'll see when you open it. I'm trying to make it as simple as possible for him to use, plus it's a good learning curve.

    If possible, can you look through it and let me know what other mistakes i have made and adjustments reqired?