Help with macro to copy new data into my worksheet

I have a pretty complex workbook that I am going to try to explain the best I can. If I select the "Create Field Book" button on the "Merge" worksheet, the macro does a web query to find a list of work items for a construction project. This data is pasted to the "Data" worksheet. The macro then looks at the list on "Data", finds a template in my template folder for each work item, and then creates a new worksheet for each work item in the list using this template. While it creates this worksheet for each work item, it fills in some header information for that work item that also comes from "Data". The sheets are ordered by the digits that appear before the first underscore in the tab name with the numbers ranging from 1 to 999. Sometimes new items are added to the list and I have another macro that does the same thing but only brings in the new items that were added. This works great until an item is added that goes before one that has already been created. For instance, if I have an existing sheet that starts with 901 and then I add a new sheet that starts with 8. This messes up my header information because the cells for that data are formulas that reference the "Data" sheet. So now the old 901 item references where the 8 item is because that sheet was not changed when the "Data" sheet was updated with the new items. So now the 901 and 8 work item reference row 18. Is there a way to fix this where the 901 item references row 19 after bringing in the 8 work item? Keep in mind that the number of sheets and the number of the work items can vary greatly so it would have to be a dynamic solution. I hope this makes since. I have attached my templates and the workbook where I am having this issue. Also the code for the problem macro is below.

Please Login or Register  to view this content.

Originally Posted by JohnTopley

I would change as follows:

In the TEMPLATE sheets: put "Item Code" in D4 e.g CI-107-0010 (can be derived from sheet name)

then use INDEX/MATCH to retrieve values from DATA based on "Item Code"

e.g

in D2

=INDEX(Data!$A$11:$H$100,MATCH($D$4,Data!$C$11:$C$100,0),1)

Change highlighted for other fields

e.g

D9

=INDEX(Data!$A$11:$H$100,MATCH($D$4,Data!$C$11:$C$100,0),8)

Any changes in DATA are then automatically reflected in the template sheets

I got this to work with a slight modification. I made the formula match the line item # to help in the cases where there were multiples of the same item code. Thanks for your help.