Using Macros to extract data from multiple Excel Wb's located in an independent directory and deposit that data into a 'Master' file

[Britoucan92]

Hi there,

I am looking for assistance in automating a task in my job. I am a novice when It comes to using VBA/macros and so far I have been unable to get answers to my problem through browsing the website.

My objective is the following:

I have a folder directory in which daily excel workbook reports are deposited from an automated system (the quantity in this folder will grow over time). These work books have the exact same worksheet format:

1) data populated in columns A -> V
2) Each column has its own unique header
3) The # of columns remains fixed whereas the # of rows can change in each work book
4) The worksheet in each workbook to which the data comes from is constant and titled 'Page 1'

I have a 'Master' data file to which i require the macro to move the data from the reports under the following conditions:

1) Data to be deposited onto a single sheet within the workbook named 'summary'
2) The data will all come from the same directory - I require the macro to automatically loop through files in the pre-defined directory
3) The data must populate columns A-> V (just like the input data workbooks)
4) The data must be inputted in row 2 onwards - Row 1 will have predefined headers in each column (Data to begin in A2 - > V2)
5) I will have formulae in columns W onwards therefore I can't have the macro clear the entire workbook before importing data. It must only update and populate the available range (A2:A999999 - > V2:V999999)

The image attached to the thread shows the layout of the source data in each workbook.

Hopefully someone can help.

Kind Regards

Britoucan

 

[Jon Peltier]

This is really a job for Power Query.

I'm not at my computer now, so I can't give precise instructions. But you could Google for 'Power Query workbooks from folder' and I'm sure you'll get many search results.