Consolidate data from 10 different workbooks

[KJones97]

Greetings,

I need help building a macro that gathers information from 1 workbook with a variable name and consolidating it into a tab on a different workbook.

For the source file the data I need is in cells (O11, O13, O17:O20, P11, P13, P17:20) on the first sheet titled "Valuation Detail". As for the workbook name, however, I will be creating new workbooks on a monthly basis, so I would preferably need the macro to work without me having to go in every month and change the file path. The file will always be named first by the date (formatted yymmdd) followed by " HC896 Template Output".

This data needs to be copied to a workbook titled Destination.xls within the same file folder to the sheet titled "HC896" in the same order to cells (D8, D10, D14:17, E8, E10, E14:E17) Ex. O11 -> D8, O13 -> D10, O17:O20 -> D14:D17.

I am hoping to be able to input the date desired for the data pull into cell J2 within the Destination.xls workboo "HC896" tab and have the macro find the proper file.

The file path where the files are located is: "F:\Desktop\BONDS" where I will place the new data each month and update the date before the file name " HC896 Template Output" (yymmdd)

I hope this is enough hypothetical information to enable you all to help me with the code.

Thank you all very much in advance.

Regards,

KJones

 

[Eric W]

Welcome to the forum.

Open a copy of your Destination.xls workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to create a module. Paste this code into the module:

Sub GetData()
    Application.ScreenUpdating = False
    Workbooks.Open Filename:="F:\Desktop\BONDS\" & Range("J2").Value & " HC896 Template Output.xlsx"
    Range("O11:P11").Copy ThisWorkbook.ActiveSheet.Range("D8:E8")
    Range("O13:P13").Copy ThisWorkbook.ActiveSheet.Range("D10:E10")
    Range("O17:P20").Copy ThisWorkbook.ActiveSheet.Range("D14:E17")
    ActiveWorkbook.Close
    Application.ScreenUpdating = True
End Sub

Press Alt-Q to close the editor. You will have to save this workbook as a macro-enabled workbook. Go to your HC896 sheet, enter the date in J2, and press Alt-F8. Select GetData and press Run.

That should do it. There's no error checking in the macro, which we can add later, but it should work. We can add a button to execute the macro too, either on the sheet or the Ribbon. Let us know.

 

[KJones97]

Hi Eric,

Thank you so much for the help and the fast reply.


The excel runs with no errors and with the data source file open the macro closes the file so I know that is working properly. However, No data gets posted to the HC896 sheet.

KJones97