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

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
      
    'Copy Data from workbook into master sheet
    
    
    'Save and Close Workbook
      wb.Close SaveChanges:=True
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      myFile = Dir
  Loop

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

[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

(ParameterName as text) =>
let
    Source = Excel.CurrentWorkbook(){[Name="tbParameters"]}[Content],
    Row = Table.SelectRows(Source, each ([Parameter] = ParameterName)),
    Value = if Table.IsEmpty(Row) = true
        then null
        else Record.Field(Row{0},"Value")
in
    Value

2. fnDataSheet1

(filepath) =>
let
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    Sheet = Source{[Item="DataSheet1",Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(Sheet),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

3. fnDataSheet2

(filepath) =>
let
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    Sheet = Source{[Item="DataSheet2",Kind="Sheet"]}[Data]
in
    Sheet

4. CombineAllFiles

let
    Source = Folder.Files(fnGetParameter("Folder Path")),
    #"Merged Columns" = Table.CombineColumns(Source,{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FilePath"),
    #"Removed Other Columns" = Table.SelectColumns(#"Merged Columns",{"FilePath"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns", "Custom1", each fnDataSheet1([FilePath])),
    #"Invoked Custom Function1" = Table.AddColumn(#"Invoked Custom Function", "Custom2", each fnDataSheet2([FilePath])),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Invoked Custom Function1", "Custom1", {"Name", "Number", "Data"}, {"Name", "Number", "Data"}),
    #"Expanded Custom2" = Table.ExpandTableColumn(#"Expanded Custom1", "Custom2", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom2",{"FilePath"})
in
    #"Removed Columns"

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.