Hi All,

Master File I refer to is this Pro Fee Tool.xlsm

I know this is possible within VBA and it would help me indefinitely but the coding behind this is too complex for me to write from scratch myself, I need a kickstart so I am calling out to any VBA expert or Guru’s out there (Perhaps you don’t need to be an Expert). I am coming to this forum because you guys are amazing helpful people !

Context:
I have a master file that I have shortened and simplified for you guys. It is called “Pro Fee Tool” above. In this file in column A all the way down for a good several thousand rows will contain a unique ‘SOW – Name’. The rest of the table to the right is empty and I need it to populate with the respected data, this would take years manually, and I get this on a monthly basis its not just one time so I need a macro.

Now, for each of the 'SOW - Name' rows there will be a separate file pertaining to that unique ‘SOW – Name’ where I can pull this data from (attached below). The files are all stored in the same folder with the same filepath just the Workbooks vary in name, with the exact same string as column A with .xlxs at the end, so the folder contains 1000’s of files like this [String from Column A].xlsx

I have uploaded an example of the data workbook here
Payments ADM.xlsx

The Goal:
I would like to have a VBA code to automate this process, the code would use the FIND function or whatever you feel is most efficient MATCH? HLOOKUP? and take that string in Column A to open the associated file to that SOW Name, and populate the columns on the right with the respected data.

The SOW – Name in the Pro Fee Tool File will not always be 5 rows apart, some will be more some less. In the actual file I am working with you see why but I removed that part for the sample as it gets complicated. But regardless of them being separated at a variable amount of rows I know that using a Find, Match and Offset function that the data can be pasted in the right spots. The column headers in the Data Sheet are identical to that of the Pro Fee Tool ones so using these Find, match, hlookup functions will work. Also every single data sheet will be the exact same formatting just the data will change based on the SOW Name.

Filepath for the first ‘SOW – Name’ called “Payments After Market – DM” is C:\Users\lougs7\Documents\Profee Tool Data Files\Payments After Market – DM.xlxs

the only variant in the file path is the “Payments After Market – DM” which will depend on whatever is in Column A.

I tried to be very descriptive, sorry for the long post . Are there any Gurus out there that can help me?? Anything would help Thank you in advance for taking your time to help me your an awesome person !