Excel VBA, Consolidate into Master Sheet

[garandleb]

Hi,

I'm trying to consolidate multiple workbooks into the a master sheet, and I'm trying to do the following
Loop through all excel files in directory
Copy specific cells in Datasheet1 from all excel files and transpose into columns A:C in the mastersheet
Copy all rows in Datasheet2 from all excel files to the master sheet into columns D:G
Repeat the transposed cells into all copied rows
Copy any new data from new sheets into last row+1


I managed to find the loop function online that serves the purpose, however i'm having trouble with the copy/paste
Any help is appreciated

Attached is sample excel for data and mastersheet

Sample Data:

File 1 Name: DataFile 1
Sheet 1: DataSheet1

Column 1	Column 2
Name	File1 - A
Number	File1 - B
Data	File1 - C

Sheet2: Datasheet2

Column 1	Column 2	Column 3	Column 4	Column 5
File1 - D	File1 - E	File1 - F	File1 - G	File1 - H
File1 - I	File1 - J	File1 - K	File1 - L	File1 - M

All files in the directory have the same structure

Master Sheet in specified workbook

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8
File1 - A	File1 - B	File1 - C	File1 - D	File1 - E	File1 - F	File1 - G	File1 - H
File1 - A	File1 - B	File1 - C	File1 - I	File1 - J	File1 - K	File1 - L	File1 - M
File2 - A	File2 - B	File2 - C	File2 - D	File2 - E	File2 - F	File2 - G	File2 - H

Please Login or Register  to view this content.

[Olly]

It would be much easier to use Power Query...

[Olly]

Here's one approach, using Power Query. This uses a Parameters table to store the folder path (you could of course use VBA to edit this value, if you want to use the folder picker dialog)

There are four queries.

1. fnGetParameter

Please Login or Register  to view this content.

2. fnDataSheet1

Please Login or Register  to view this content.

3. fnDataSheet2

Please Login or Register  to view this content.

4. CombineAllFiles

Please Login or Register  to view this content.

This will combine the data from all files in the folder specified in the parameters table, according to the specification in your first post. Now you can simply "Refresh" the data table, and it will combine the data from all source files.

See attached file for worked example.