VBA loop to open workbooks, copy and paste data

[meganduerk]

Hi,

I'm hoping I can get some helping with adding VBA code to my workbook that will merge data from multiple "detail" workbooks to one consolidation workbook. I have a list of file paths, names and sheet names in my consolidation workbook. I would like the macro to follow the first "detail" file path & open the first file that I have listed in the consolidation file, go to the specified sheet, find the column which contains x and the row the contains y (x and y are the same in all files) and copy everything to the left and above of x and y, and then paste in the specified sheet in the consolidation file, close the "detail" file and then repeat for the second file in the list and so forth. And then hide each of the consolidated sheet names in the consolidation workbook.

[TABLE="width: 500"]
<tbody>[TR]
[TD]File Name[/TD]
[TD]Detail Sheet Name[/TD]
[TD]Consolidated Sheet Name[/TD]
[TD]File Path[/TD]
[/TR]
[TR]
[TD]Department 99[/TD]
[TD]2016CY Actl[/TD]
[TD]99 - General[/TD]
[TD]'\\data\corp\actl\[Department 99]2016CY Actl'![/TD]
[/TR]
[TR]
[TD]Department 76[/TD]
[TD]2016 CY Actual[/TD]
[TD]76 - Mkt[/TD]
[TD]'\\data\corp\actl\Mkt\[Department 76]2016CY Actual'![/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Thank you in advance for your help!

Megan