VBA to consildate data from multiple workbooks to one workbook

[Tom07789]

I have multiple workbooks, each with a HIDDEN tab called 'Data Merge' located on my H drive within a folder called 'KPI Submission'.

I want all data from the 'Data Merge' tab using range A2:S41 to be copied to a master excel file called 'KPI Data Extraction'

Loop until all files within the KPI submission folder has been consolidated.

Thanks in advanced!

 

[tonyyy]

Tom07789,

You might consider the following...

Sub AnotherMaster()
'''' The workbook "KPI Data Extraction" must be open when running this macro
Application.ScreenUpdating = False
Dim wb As Workbook, wb2 As Workbook, wb3 As Workbook
Dim FolderName As String, fileName As String
NextRow As Long

Set wb = ThisWorkbook 'Workbook with this macro
Set wb3 = Workbooks("KPI Data Extraction.xlsx") 'Change ".xlsx" to match your file extension
FolderName = "H:\KPI Submission\"
fileName = Dir(FolderName & "*.xls?")

Do While fileName <> ""
    If fileName <> wb.Name And fileName <> wb3.Name Then
        NextRow = wb3.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Set wb2 = Workbooks.Open(FolderName & fileName)
        With wb2.Sheets("Data Merge")
            .Visible = False
            .Range("A2:S41").Copy Destination:=wb3.Sheets(1).Cells(NextRow, 1)
            .Visible = True
        End With
        wb2.Close savechanges:=False
    End If
    fileName = Dir
Loop
Application.ScreenUpdating = True
MsgBox "The dishes are done, dude!"
End Sub

Please note the code is untested.

Cheers,

tonyyy