Compiling Workbook Using Information From Different Files - $$

[pynergee]

I am looking to compile a workbook using information retrieved from different files.

Basically, I have created a "Template" that I am trying to populate and format using information based on other files. The tasks I want to complete are:

-Fill out Part Numbers in specific cells taken from the name of a different file.
-Insert Bill of Material information and special notes from a different sheet into the template
-Using a cross reference from a sheet that contains operation information, to remove the operations in the template that are blank in the reference sheet.
-Remove procedure sheets from the template if they are not called out in the cross reference sheet before, and retain those that are.

And finishing by save as the template to a different location without overwriting the template. I hope to have this macro to run through an entire list.


I have attached a .zip file with pertinent examples.
The folder "TravellerSamples" contains files which contain the information for the BOM and Special Notes. All of this information can be pulled from the first sheet in each workbook. The BOM is to be pulled from the information after "No.". I would like to grab the first part no, all the way to the last one (until the first blank), and all of the information up until the Qty. (No. Column + 3 for the range). The Special Notes are captured from the information below "Comment", and I seek to capture each until the first blank after it in Column A.
The WIsample.xlsm is the template that I would like information to be populated. On the first sheet, the "TITLE SHEET", I would like to capture the "ASSY NO:" & the Part Number (typically the file number of the BOM information), "DOC NUMBER: WI" & the Part Number. The description I will fill out myself.
On the PRODUCTION sheet in this file, this is where it gets tricky for me. First, I would like to paste in the BOM information into cell C29 (taken from the TravellerSamples files), and also the Comments/Special Notes pasted into the Notes section (H28 cell). After this, I want to delete rows as necessary per the operations taken from the last file in the zip, the WIProgressSample.xlsm.
For example, on the WIProgressSample book, column F correlates to CCWI316. Row 23 correlates to part number 600-1-25SD11, and its necessary operations for assembly. If cell F23 has a value, or is not blank, I would like to retain rows 11 and 12 on the WIsample.xlsm for that part number. Similarly, if F23 is blank, then delete and remove rows 11 and 12 from the WIsample.xlsm book.
Similarly, columns S, T and U relate to the sheets CCPR112, CCPR118, and CCPR115 respectively. If there is a blank for each of these, I would like them to be deleted from the WIsample sheet.

After all these operations are done, I would like to SaveAs in a different location, under the file name: "WI" & Part Number.


So far, for code, I have these two, which should copy the desired BOM, but I do not yet have it set up to paste into the WIsample book.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Also, I should note, that I am willing to tip monetarily (USD), along with the standard + reputation, for the person who is able to help me accomplish this the most.

Thanks for the help!