VBA Help : Update Summary Spreadsheet from multiple excel transmital list

Hi ALL,

I would like to ask help and guide me on how to create a vba to update my summary list from multiple transmittal files.

Workflow:
1. Transmital number will be inputed manually on column A1, A2 ... ect
2. Extract values from individual transmittal files to columns C:E
3. Count if the values in range D9:xxx satifies the criteria in F2:N2

Thank you.

SUMMARY.jpg
Trans1.xlsx
Transmittal Summary.xlsm
Trans2.xlsx

Hi rxk,

Try this:

Please Login or Register  to view this content.

You will need to ammend the part highlighted red to the path where the Trans1 etc. files are kept

Hi spitfireblue,

Thank you for the code. I modified the the path to Path = ActiveWorkbook.Path & "\" & "Transmittals" & "\" to be flexible on the file location.

Is it possible to add code to check any missing trans file under Transmittal folder and pop-up a list of missing files.

Thank you.

Hi,

Two options... both very similar. The one below will list all missing files after the code has been run. Otherwise, if you remove everything highlighted blue it will pop up with a message for each missing file.

Please Login or Register  to view this content.

Thank you. splitfireblue

Glad I could help