VBA to Create new workbook, worksheets, folder

  • I have a workbook (attached below), includes three (3) worksheets, which will be used as a template, to be copied into a new workbook and/or worksheets. The number of new workbooks and/or worksheets will be based on a table for "stations" as attached in the "Start" Worksheet.

    I intend to add two (2) buttons, which I need to automate as follows:


    Button 1 click:

    1- Create New Folder with the ORDER NAME cell

    2- Under that folder, I need to create a new workbook for each station (i.e. workbook's name is "Station 1" for Station 1 and so on),

    then copy all the 3 worksheets templates below (i.e. First_Sheet, Second_Sheet, and Third Sheet). Copy the values and the formatting NOT the formulas

    3- Repeat step 2 for the rest of the stations until the last station.

    4- Desired outcome: A new workbook for EACH station, each contains a copy of the 3 worksheets.

    In this particular example, I expect 7 workbooks as an output for the seven stations, but there could be any number of stations up to 30

    Button 2 click:

    Exactly Same as Button 1, EXCEPT:

    Create just ONE WORKBOOK includes ALL the worksheets for ALL the stations in the same workbook

    Worksheets naming convention will likely be something like Station 1_First_Sheet, etc.

    I appreciate any help; preferably a working, efficient, and fast running code, which will definitely make my weekend !!:)
    Create New WorkBook_Worksheets.xlsx

  • Thanks RoyUK,

    Yes, you are right!... What you mentioned is just one option, the other option as stated in my post is to create an independent workbook for each station...

    I need to have both features and will choose what I will use depends on the scenario that I have. If one workbook with (let's say) 30 or 40 worksheets, realistically this would not be favorable. On the other side, if I have just two stations, it could be feasible...

    The other benefit that I am looking for is that I am learning the programming techniques in doing both options...

    Thanks for jumping in, and I hope I can get this resolved... All the best :)

  • Just a question - it would be easier to be able to copy the "template" sheets as is if there were no formulas in them as you've requested to copy without formulas. It is feasible to copy without formulas but is there some reason the formulas are in the template sheets if they are not going to be used in the workbooks to be created?

  • Thank you very much RoyUK

    While it does the copy very well, however, the output files in the C: drive still have the formulas (!). I reattached the solution filled with some numbers in First_Sheet as a test. The same cells in the output files still contain the formulas.

    The reason I need to remove the formulas is that the actual worksheet that I have contains many formulas linked to other worksheets not included in this example. I needed to save a copy of the final results for each station as a value only; not worrying about links to external sheets which would not be the same for each station...

    Thanks a lot!

  • The formulas should be changed to values with these lines

    1. ActiveWorkbook.Sheets.Select
    2. Selection.Cells.Select
    3. Selection.Copy
    4. Selection.PasteSpecial Paste:=xlPasteValues
  • Hi RoyUK...
    Please try the workbook that I attached in my response above, then look at the first_Sheet of any of the output files...
    Formulas still show in the formula bar as I click on any cell that has numbers. Please let me know if I am missing anything... Thanks again

  • Try this, it has code for both buttons and the formulas are saved as values

  • Thank you very much royUK

    I think we are getting there !!. It is almost working perfectly except for these two little glitches as follows:

    1- For some reason, it is still coping with the formulas, which would potentially remain linked to the original workbook, the matter that I need to avoid by copying just the values and formatting

    2- I need to limit the copying to those 3 worksheets. In this context, it is working as such with the individual stations' option, but it is still copying the other sheets within the "one Workbook option"

    Please have a look at the attached final revision as a start, in which I added few formulas (highlighted in RED) and a couple of worksheets for testing... Please check the OUTPUT files for each option

    I know how much effort you did already, which I appreciate... Thanks again RoyUK!!!

  • I put a simple test formula in the master workbook. After running the code that formula is removed.

    The create workbooks macro creates seven workbooks each containing three sheets.


    The other benefit that I am looking for is that I am learning the programming techniques in doing both options...

    If you are trying to learn, then you haven't studued the code. I missed amending the code to convert to values in the workbooks macro.

  • Pleased to help.

    Post back if you need further help.

    Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.