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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...