VBA Help. Creating new workbooks from templates and importing content


New Member
Feb 21, 2018
Hi all,

So I've started working with VBA a bit and have figured out what I'd like to do and that it is possible, I'm just not skilled enough to be able to write it from scratch.
I've always found it easier for me to work with existing code and learn what everything does by modifying parts of it to do what I need and am hoping that some kind soul will help me get up and moving.

I have created templates for several stages of a process, each with some calculations and everaging etc. specific to that stage, that is all locked and hidden on those templates and am looking to have a main workbook that pre-populates that information and updates it if it is changed on the next stage, allowing me to track updats on a single page, but also simplifying the input.

The tempaltes tend to use information from the other earlier stages to improve the accuracy with each next stage and to save repetition.

This is what i've come up with as far as how it would work:

There is a "MasterWorkbook" that has some information initialy entered to cover the basics (customer name, owner, start/end dates etc.). it also has a "status" field, which should default to "new" when data is entered into the customer name field (I can do this bit easily enough with statements in the work book.
Each row has a button next to it, which when pressed, looks at the "Status" column for that row and determines which templated workbook is needed, or, what workbook it is looking for to extract data from.

I'll run through a sscenario so you get the idea:

If the status is "new" it creates a new workbook, using a template called "Estimator" from a hidden sheet on the main workbook, calling it "esitmator_Customer" where "Customer" is pulled from a column on that row.
When that workbook is created it changes the status to "estimating"

If the button is pressed again, it will read the status as "estimating" and look for that workbook in the same folder as the main workbook is currently in. if it doesn't find it, it generates it again.
If it does find it, it copies the data from that worksheet into the main workbook and changes the status to "estimated"

If the button is pressed and the status is "estimated" it creates a "planner" workbook, using the "planner" template from a hidden sheet on the main workbook, calling it "planner_Customer" and changing the status to "planning"

From then on throughout the rest of that rows lifecycle it repeats this same behaviour, as it steps through the stages, importing informaiton into the main workbook or generating the next document based upon a template and the infromation it already has.

I hope what I'm looking to do makes some sense and if you can point me at any resources or example code that may be of use in my probably over complicated quest, it would be greatly appreciated.



Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...