Extract data from multiple excel files onto one sheet

[Excelhelp77]

Hi all, I am wondering how I can use VBA to extract data from multiple excel files onto one summary workbook that pulls the data from the most recent date. All of the excel files have different names but each worksheet is labeled "Financials". Each worksheet is formatted almost identically - all of the metric names are in column C and the data starts in column D, but not all of the rows are identical. I would like the Summary workbook to be able to reference a formula in order to pull the correct data. A formula that works in each of the workbooks is "=INDEX($D$1:$R$350,MATCH("Debt Service Coverage",$C$1:$C$350,0),MATCH(MAX($D$1:$R$1),$D$1:$R$1,0))". I would simply change the "Debt Service Coverage" to the other corresponding metrics that I am interested in. I am also wondering if I could have this Summary workbook automatically update when opened and have the date that the data is being pulled from for each workbook as it may be different. Thank you in advance!

 

[hiker95]

Excelhelp77,

Welcome to the MrExcel forum.


See in one of the below will help you get started in the right direction:

WBs to 1 Sheet
https://sites.google.com/a/madrocket...s-to-one-sheet

WBs to Sheets
https://sites.google.com/a/madrocket.../wbs-to-sheets