Excel macro that will fill cells with data from a source document to a new workbook template

[janesvilleman]

Hey Everyone,

I am new to this site and want to thank anyone who takes the time to look at this.

For my job, we are given a list of items that have a corresponding item number, item code, unit, quantity, etc. We take these individual items and we take the information and put it into a template summary sheet to track quantity to pay the contractor. Here is an example of how it is laid out:

[TABLE="width: 1278"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 750"]
<tbody>[TR]
[TD]Item Description[/TD]
[TD]Units[/TD]
[TD]Item Code[/TD]
[TD]Prop. Line[/TD]
[TD]Quantity Placed[/TD]
[TD]Quantity Paid[/TD]
[TD]Authorized Quantity[/TD]
[TD]Initial Quantity[/TD]
[TD]Pending Changes[/TD]
[TD]Unit Price[/TD]
[/TR]
[TR]
[TD]Removing Old Structure[/TD]
[TD]LS[/TD]
[TD]203.0200[/TD]
[TD]0060[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$150,000[/TD]
[/TR]
[TR]
[TD]Debris Containment[/TD]
[TD]LS[/TD]
[TD]203.0225.S[/TD]
[TD]0160[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$0.01[/TD]
[/TR]
[TR]
[TD]Exc For Structure Bridges[/TD]
[TD]LS[/TD]
[TD]206.1000[/TD]
[TD]0660[/TD]
[TD]0.6[/TD]
[TD]0.6[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$13,883.85[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


The list goes on. "Item Description" is in cell A1 for reference.

What we then do is take the item and make its own sheet in a new workbook that is the same template for each item. The information used is Item Description, Units, Item Code, and Prop. Line. This information goes into the same cell on the template for each item. Usually this is a short process to do it manually for a smaller size job. These big jobs have over 1800 items. That would take weeks to create them all manually.

Is there a macro that can be written or other way to take the row of information and fill in the template sheet for each individual row of information in an entire spreadsheet? The template looks like this where "ITEM:" is in cell A1 for Reference:


[TABLE="width: 1000"]
<tbody>[TR]
[TD]ITEM:[/TD]
[TD]**Fill Item Description Here Cell B1&C1 Merged[/TD]
[TD][/TD]
[TD]Project ID[/TD]
[TD]1005-10-77[/TD]
[/TR]
[TR]
[TD]ITEM #:[/TD]
[TD]**Fill Item Number Here Cell B2&C2 Merged[/TD]
[TD][/TD]
[TD]ROADWAY[/TD]
[TD]IH 39[/TD]
[/TR]
[TR]
[TD]CATEGORY:[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]DESCRIPTION[/TD]
[TD]HIGHWAY[/TD]
[/TR]
[TR]
[TD]ENTERED BY:[/TD]
[TD]CRT[/TD]
[TD][/TD]
[TD]COUNTY[/TD]
[TD]ROCK[/TD]
[/TR]
[TR]
[TD]CHECKED BY:[/TD]
[TD]MCW[/TD]
[TD][/TD]
[TD]PLAN QUANTITY[/TD]
[TD]1 LS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]**Fill Units Here* Cell C6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Another thing we do (if this can be included in the macro, not a huge deal) is we name each new file as Propline_ItemNumber_ItemDescription. We can do that manually if needed. We still have to go into each sheet and add certain things anyways. The file path to save each new sheet will be a new folder on my desktop.

Me being super green to all this, could anyone who helps please put all the steps in the macro so that if i need to change a range or tweak something, I can do it easily? I don't mean to be needy, but like I said, I just started looking at this a week ago so be gentle!

Thank you in advance!