Need VBA help to create copies of template and populate sheets with data from Master

[wannabburly]

I am trying to create a workbook where:
-Data is entered daily into a Master sheet
-Copies of a Template sheet are created and named based on data in Col A
-Newly created sheets are populated with data from the same row as the unique data in Col A
-Data from the current day replaces the previous data on the created sheets and moves the previous day's data to another place on the same sheet (In other words, don't create a new sheet everyday if a sheet for that Job No. already exists. Also, each sheet should be a current/historical look at the ongoing project (Job No.) so there are charts in the template that will update/progress from each day's data.)

Master Sheet would look something like this...

Job No.	Customer	Location	Data 1	Data 2
Unique.1	Aa	Here	one	5
2.Unique	Bb	There	two	6
3.Unique	Cc	Every	three	7
Unique.4	Dd	Where	four	8

First created sheet is a copy of the template, would be named "Unique.1" and populated with data from the 1st row.
Second created sheet is a copy of the template, would be named "2.Unique" and populated with data from the 2nd row.
etc. etc.

Also, the location of the data on the created sheets is different than on the Master sheet, so its not just a "filter Master then copy & paste" type scenario.

I'm open to any and all suggestions.

Thanks in advance.

[SamT]

1. I am trying to create a workbook where
2. -Data is entered daily into a Master sheet
3. -Copies of a Template sheet are created and named based on data in Col A
4. -Newly created sheets are populated with data from the same row as the unique data in Col A
5. -Data from the current day replaces the previous data on the created sheets and moves the previous day's data

lay out the Template sheet for human viewing, ie. formatted as you please

Instead of one master sheet, use 2 sheets as a database. ie No formatting except the Header Row should be Font = Bold & one size larger and has a bottom border.

Sheet Jobs DB, should have Fields (Headers) for all job pertinent info including an Active/inactive Field, except that the Customer Field should be "Customer ID" and the DB should not include any other Customer Info.

Sheet "Customer DB" first Field, (Column A) should be "Customer ID" and the VB should include all customer Info.

This provides for Business analysis of Jobs without Customer info getting in the way and Marketing use of Customer Info without Job info getting in the way as well as providing permanent centralized records of both, even when inactive Job specific sheets are moved to Archive.

In VBA, create a NewJob UserForm that has input Controls for all Static Fields (Customer Info, Job Number, Location, etc. We can help you with encoding this UserForm to put all the Info in the right places in the DB sheets and creating and filling out a new Job Sheet from the Template.

In VBA, create a Daily JobDataInput UserForm with Controls to select an Active job from the Jobs DB and controls to input daily dynamic field info. We will help you with the code to make the contols work, you just need to get the prototype done.

A project I am working on can be found at Post Holding thread for my own use. It's is a lot of reading, but the Project is about how to design and create workbooks like yours.

Heh, I just put a link to this thread in there.

[wannabburly]

Is there a particular post in that thread that I should look at?

I should add that I have found code that will copy the template as many times as data appears in Col A, name the sheets appropriately, and put Col A data into the template. I'm having trouble getting the other data from the rest of each row into the created sheets. The append data and move previous data part, I haven't gotten anywhere with yet.

[SamT]

That thread is not yet organized. At first I copied posts to it, then I started just linking to relevant posts and threads

The entire thread is about designing a robust and easily maintained Project as opposed to just pasting together a hodge-podge of code that kind of works.

It is your choice how you want to develop your Project.

[wannabburly]

1. I am trying to create a workbook where
2. -Data is entered daily into a Master sheet
3. -Copies of a Template sheet are created and named based on data in Col A
4. -Newly created sheets are populated with data from the same row as the unique data in Col A
5. -Data from the current day replaces the previous data on the created sheets and moves the previous day's data

lay out the Template sheet for human viewing, ie. formatted as you please

Instead of one master sheet, use 2 sheets as a database. ie No formatting except the Header Row should be Font = Bold & one size larger and has a bottom border.

Sheet Jobs DB, should have Fields (Headers) for all job pertinent info including an Active/inactive Field, except that the Customer Field should be "Customer ID" and the DB should not include any other Customer Info.

Sheet "Customer DB" first Field, (Column A) should be "Customer ID" and the VB should include all customer Info.

This provides for Business analysis of Jobs without Customer info getting in the way and Marketing use of Customer Info without Job info getting in the way as well as providing permanent centralized records of both, even when inactive Job specific sheets are moved to Archive.

In VBA, create a NewJob UserForm that has input Controls for all Static Fields (Customer Info, Job Number, Location, etc. We can help you with encoding this UserForm to put all the Info in the right places in the DB sheets and creating and filling out a new Job Sheet from the Template.

In VBA, create a Daily JobDataInput UserForm with Controls to select an Active job from the Jobs DB and controls to input daily dynamic field info. We will help you with the code to make the contols work, you just need to get the prototype done.

A project I am working on can be found at Post Holding thread for my own use. It's is a lot of reading, but the Project is about how to design and create workbooks like yours.

Heh, I just put a link to this thread in there.

I'm starting from scratch and working on the 2 DB sheets you suggested above. Question, for the "Customer DB" sheet, I can't think of any Customer Info I need besides "Customer ID", everything else I want to track will be a unique combination specific to the individual job, do I still need to create this sheet?

Based on the work I had done on the other workbook, my Template sheet is formatted appropriately.

[SamT]

I was a contractor for years and I kept records of Customer name, address , ph#'s, Emails, spouse info kids, b-days, anniversaries, et al, Bank account numbers and any and all info I could get. For commercial clients and Subs, I added dept heads and phone numbers, especially Accounting, Purchasing, and Billing.

Of course, in my company I was the CEO, CFO, CIO, CAO and CZO, and the Head of Accounting, sales, purchasing, marketing, Projects, and Lead Carpenter and Chief Broom Operator.

Tell me more about your specific situation and I can give more specific advice.

Do you already have a Customer DB elsewhere?

This provides for Business analysis of Jobs without Customer info getting in the way and Marketing use of Customer Info without Job info getting in the way as well as providing permanent centralized records of both, even when inactive Job specific sheets are moved to Archive.

If none of that applies or you already have a Customer DB elsewhere, then you don't need a Customer Table. (Colloquially, DB and Table can be interchanged. Specifically, a Data Base can contain many Tables.)

[SamT]

Based on the work I had done on the other workbook, my Template sheet is formatted appropriately.

Since the layout of the UserForm is based on the Template, why don't you upload a Workbook with just the Template sheet in it?

[wannabburly]

I was a contractor for years and I kept records of Customer name, address , ph#'s, Emails, spouse info kids, b-days, anniversaries, et al, Bank account numbers and any and all info I could get. For commercial clients and Subs, I added dept heads and phone numbers, especially Accounting, Purchasing, and Billing.

Of course, in my company I was the CEO, CFO, CIO, CAO and CZO, and the Head of Accounting, sales, purchasing, marketing, Projects, and Lead Carpenter and Chief Broom Operator.

Tell me more about your specific situation and I can give more specific advice.

Do you already have a Customer DB elsewhere?If none of that applies or you already have a Customer DB elsewhere, then you don't need a Customer Table. (Colloquially, DB and Table can be interchanged. Specifically, a Data Base can contain many Tables.)

Ahh, this doesn't have quite that much scope and is an internal project focused around a smaller group within our organization.

We provide services for drilling oil & gas wells. We need a way to collect daily performance data (which we get from our field personnel), track progress, compare against historical performance, present this information in a visually pleasing/intuitive (charts, internal marketing, etc.) deliverable, and distribute it to various people in our organization.

I'm sure our accounting group has a more thorough customer database. For the purposes of this project (for now), Customer name or ID should be fine. I do have a list of every customer we've worked with in the last 2 years which is more than enough to start with.

Since the layout of the UserForm is based on the Template, why don't you upload a Workbook with just the Template sheet in it?

The order of columns on the "Jobs DB" sheet of the attachment is the sequence of the source data. You can see the difference between where it is input and where it will end up on the "Template" deliverable.

Some data are used for calculations, some raw data is presented as-is.

[SamT]

Lucky you, I also worked for a Fishing Company in Venture Ca, as a Gas Lift Valve and Mandrel tech.

For the purposes of this project (for now), Customer name [AND] ID should be fine.

I have attached a workbook of lists and a Database:

Wells_DB, is a Database, with only the Status Field dynamic. Ie: once a Well is added, it never leaves the list.

The VBA_Lists sheets is also static with only new Records being added as the arrive. Ie, All items in the lists are permanent.

I tried to make the instructions understandable, but you know how to reach me if you have questions.

Now, I am going to ponder on the design of the Data Input UserForm.

[wannabburly]

Ok...

So on my other workbook, on the "Input" sheet, the Customer, Rig, County, Basin, and Region were all Data Validation drop downs. And they weren't dependent on each other. I was eventually going to include some filtering there, but haven't yet. Does hierarchy matter? Looking at the "Idaho" sheet, I'm assuming it does. In which case, proper hierarchy for location would be Region > State (though we don't really use this as a qualifier) > Basin > County.

Can you explain why the Wells_DB and VBA_Lists sheets are separate? In my mind, the information contained in the separate sheets must be combined to identify the project.

Whenever this workbook is functional, it will be a "this day forth" kinda thing with Active projects and the data entered will grow each day. I don't want to spend too much time building this with historical data as we have other systems that store much, more, detailed project data upon completion (I won't say that they do it very well, but its all there). The goal for this exercise is to have a way to provide a daily project update on 20+ wells and compare them to a top performer based on some criteria where daily user input time is <1 hour.

[SamT]

They both feed the Drop down lists on the UserForm. They can both be on VBA_lists if that is your preference. Now that I took the "Areas" list out of VBA_Lists, there is plenty of room. Do you see ever needing a "State" option at this time? Leave it out. Oh, yeah, add a Status List, please.

The UserForm DropDowns, (ComboBoxes) All the user to either scroll and select, or Type and watch the list scroll till it matches what he/she is typing. If there is no match, then the drop down allows the code to add the new value to the data bases and VBA_lists.

Since you are aware of the "Areas" Hierarchy. go ahead and modify VBA_Lists and "Idaho" to reflect reality. With VBA, The ComboBoxes on the UserForm can be hierarchical. no mater which of the below inputs (not in order, ATT,) the user choose first, all the other ComboBox Lists will reflect that choice. If he/she were to choose Job Number, the ComboBoxes would only have the values applicable to the job number.

Job_Number

Status

Customer_ID

Rig_Name

Well_Name

County

Basin

Region

Assuming that the User enters a New Job number, customer, and well name, selects a Rig from that ComboBox, then selects a Region from all Regions, the remaining Area ComboBox's Lists will reflect the region, then reflect the Basin, and finally, the County ComboBox will only have the counties in that Basin. Going the other way, if he/she first selects a County, all the other ComboBoxes would only have one item in their list.

I am assuming that all Daily updates are based on Midnight to Midnight

Regarding the Template:
Is "Depth Out" the same as "Midnight Depth"
In Columns "O" to "AE" the only values required to be entered by the UserForm are Date, BHA, Rotate( hrs & Ft,) Slide (hrs & ft,) Circulate hrs, and Other hrs.

Regarding the "Pace Setter" well. Would you prefer a "permanent" Pace Setter Copy of the Template or a separate Pace_Setter DB? The Pace Setter Copy of the Template would show the Daily Updates for the Well, however the Pace_Setter DB would merely be an extract of the Well's daily values.

My thoughts are the Pace Setter Copy of the Template, with an added, non-printing Cumulative Table, (see Template, Columns "AK' to "AN,") is best, because it make both coding and formulas much easier.

Remember, when it comes to all these different sheets in the Workbook, they can all but one be hidden.

[wannabburly]

They both feed the Drop down lists on the UserForm. They can both be on VBA_lists if that is your preference. Now that I took the "Areas" list out of VBA_Lists, there is plenty of room. Do you see ever needing a "State" option at this time? Leave it out.

No state for now. My area hierarchy will be Region > Basin > County

Oh, yeah, add a Status List, please.

Job status would either be Active, Idle, or Complete.
Active - Everything updates
Idle - Nothing updates (except for maybe date) and the job is kept open
Complete - Final update (or nothing updates, depending on the coding I would assume) and the job is archived

The UserForm DropDowns, (ComboBoxes) All the user to either scroll and select, or Type and watch the list scroll till it matches what he/she is typing. If there is no match, then the drop down allows the code to add the new value to the data bases and VBA_lists.

Since you are aware of the "Areas" Hierarchy. go ahead and modify VBA_Lists and "Idaho" to reflect reality. With VBA, The ComboBoxes on the UserForm can be hierarchical. no mater which of the below inputs (not in order, ATT,) the user choose first, all the other ComboBox Lists will reflect that choice. If he/she were to choose Job Number, the ComboBoxes would only have the values applicable to the job number.

Job_Number

Status

Customer_ID

Rig_Name

Well_Name

County

Basin

Region

Assuming that the User enters a New Job number, customer, and well name, selects a Rig from that ComboBox, then selects a Region from all Regions, the remaining Area ComboBox's Lists will reflect the region, then reflect the Basin, and finally, the County ComboBox will only have the counties in that Basin. Going the other way, if he/she first selects a County, all the other ComboBoxes would only have one item in their list.

Perfect. So my highest level of area hierarchy, in my case each Region, needs to have their own sheet?

I am assuming that all Daily updates are based on Midnight to Midnight

Correct.

Regarding the Template:
Is "Depth Out" the same as "Midnight Depth"

Correct.

In Columns "O" to "AE" the only values required to be entered by the UserForm are Date, BHA, Rotate( hrs & Ft,) Slide (hrs & ft,) Circulate hrs, and Other hrs.

On the template sheet...
Column "O" is calculated based on column "P" which comes from the User Input
Column "Q" is copy & paste from "L6" as its updated from the User Input
Column "R" comes from User Input
Column "S" is a running cumulative total of column "Q"
Column "T" converts column "S" from hours to days
Column "U" updates from User Input, and it appends
Rotate Hrs & Ft append each day from User Input
Slide Hrs & Ft append each day from User Input
Circulate Hrs appends each day from User Input
Other Hrs is calculated from 24 - (Rotate Hrs + Slide Hrs + Circulate Hrs)
Column "AC" through "AE" are calculated from other data each day.
There's a mix of updating directly from the user input and things Copy & Pasting once they are on the job sheet (was going to clean this up at some point). Everything appends each day and feeds the charts.

Does that make sense?

Regarding the "Pace Setter" well. Would you prefer a "permanent" Pace Setter Copy of the Template or a separate Pace_Setter DB? The Pace Setter Copy of the Template would show the Daily Updates for the Well, however the Pace_Setter DB would merely be an extract of the Well's daily values.

My thoughts are the Pace Setter Copy of the Template, with an added, non-printing Cumulative Table, (see Template, Columns "AK' to "AN,") is best, because it make both coding and formulas much easier.

The Pace Setter is the data from a historical best performer. The current project is being measured against it. My thought is that once a Rig is selected, the Days vs. Depth plots are populated with the Pace Setter data for that Rig. As the current project progresses, we get a visual representation if we are ahead/behind/following the performance of the Pace Setter. So I guess a Pace_Setter DB is what I'm after. I have already created a workbook where each Rig has its own sheet where the performance data would be stored. This workbook has hyperlinks on the Master list sheet that go to each Rig's sheet and a link on each Rig's sheet to get back to the Master (I "borrowed" some code for that).

As for print area, the template is set up to print A1:M76 on an 11"x17" page. Everything else on the template sheet will not be printed. Since this will go to PDF, rather than hardcopy, page size really doesn't matter so long as the layout is visually pleasing.

[wannabburly]

My Area sheet is built. I went Region > State > County. Since there are only 3 Regions, I put them all on one sheet, I hope that is ok.

CustomerNames and RigNames lists are complete and on the VBA_Lists sheet.

[SamT]

Well???

Let's see it

[wannabburly]

Hopefully the changes I made, I understood and won't mess up your plans for the UserForm.

So now in the Template B9:11 are Region-State-County.

[SamT]

I don't have plans for the UserForm, just ideas. I can't make plans until I have all the details,

Some things I do know:

1. The Sheet "JobsDB" in the Workbook "wannabburly-Dashboard Ver3.xlsm" that you first uploaded is needed for easy Business Information analysis.
2. Each User of the UserForm will have their own preferred way of using it.
   
   1. Some will want to "Drill Down" thru the Region >> State >> Counties >> Wells
   2. Others will want to just use the list of Well Names.
   
   

One of the most convenient ways to update the lists is going to be using method 2.1 of that list. That is the Main reason for having it. As Users gain experience with the UserForm, they will probably use method 2.2, as it is the easiest and fastest for an experienced typist.

As you know "The more comprehensive the list, the better the Form's Interactivity will be."
Can you put the Well names in the attached by county for me. It is needed for 2.1 above.

Now the plans for the UserForm are almost finalized.

[wannabburly]

So I'm just adding the Well Names for currently active projects under each county, correct? Not putting in historic (completed project) information? And I assume the formatting shown for the example County & Well Name list needs to be duplicated

If there are no wells in a certain county, do I add a blank cell between Counties?

Forgive me if I'm not providing as much data as you would like, some of this information may or may not be confidential.

[SamT]

So I'm just adding the Well Names for currently active projects under each county, correct? Not putting in historic (completed project) information? And I assume the formatting shown for the example County & Well Name list needs to be duplicated

Correct. And I see no need for past work.

If there are no wells in a certain county, do I add a blank cell between Counties?

Yes. THe code will see all cells directly adjacent to the County name as belonging to that county.

Forgive me if I'm not providing as much data as you would like, some of this information may or may not be confidential.

If that occurs, just put a String of "x"es (XXXXXXX.) you can edit them out in your copy. We just need to know something is there.

All these List sheets are pretty static and will be hidden from the casual User once the Project goes into production.

While they can be edited manually, there are so many cross reference that it would not be hard to miss an item and mess up the system. That Is why I like having it updated automatically via a new entry in the UserForm. Code never forgets.

Have I mentioned a cross reference list WellName > Customer or Customer >WellName, whichever is easiest for you? Just for current Jobs. I do hope that you have an office Gopher doing this grunt work

After you get done, I will be redoing all these lists to a format that is going to be both easy for humans to understand and Code to use.

[wannabburly]

I'm working on getting the well names added to the county_wells sheet.

Have I mentioned a cross reference list WellName > Customer or Customer >WellName, whichever is easiest for you? Just for current Jobs. I do hope that you have an office Gopher doing this grunt work

Can you explain how you would like this cross reference list set up? This, for example, or something different...

Customer A
Well 1
Well 2
Well 3
Well 4

Customer B
Well 5
Well 6
Well 7
Well 8

etc.
etc.

[wannabburly]

Attached is the updated WellsByCounty file.

I went ahead and did all wells as XXXX, will update once I get a working document.