VBA code to merge data from files into one master file

[onche414]

Hello,

I have multiple excel files in a folder and I would like to have a code which will go through that folder and copy as value the data (from column A to Z) from the first sheet of each of the excel files and gather them in a master data file in one sheet (i.e copy data from excel file 1 then copy below the data from excel file 2, etc)

Thank you,

 

[jkpieterse]

Use Data, Get Data, From File, From Folder. No VBA needed!.

 

[onche414]

Use Data, Get Data, From File, From Folder. No VBA needed!.

Thank you for your answer, but I want to learn VBA and would like to use it

 

[jkpieterse]

Perhaps this gets you started?

Sub ConsolidateFiles()
    Dim lCount As Long
    Dim vFileName As Variant
    Dim sPath As String
    Dim lFilecount As Long
    sPath = "c:\windows\temp\"
    ChDrive sPath
    ChDir sPath
    vFileName = _
        Application.GetOpenFilename("Microsoft Excel files (*.xls*),*.xls*", _
        , "Please select the file(s) to consolidate", , True)
    If TypeName(vFileName) = "Boolean" Then Exit Sub
    For lCount = LBound(vFileName) To UBound(vFileName)
        ProcessFile CStr(vFileName(lCount))
    Next
End Sub


Sub ProcessFile(sFileName As String)
    Dim oSh As Worksheet
    Workbooks.Open sFileName
    For Each oSh In Worksheets
        oSh.UsedRange.Copy
        With ThisWorkbook.Worksheets("AllData")
            .Range("c" & _
                .Rows.Count).End(xlUp).Offset(1).PasteSpecial _
                xlPasteValuesAndNumberFormats
            .Range(.Range("A" & .Rows.Count).End(xlUp).Offset(1), _
                .Range("C" & .Rows.Count).End(xlUp).Offset(0, -2)).Value _
                = ActiveWorkbook.FullName
            .Range(.Range("B" & .Rows.Count).End(xlUp).Offset(1), _
                .Range("C" & .Rows.Count).End(xlUp).Offset(0, -1)).Value _
                = oSh.Name
        End With
    Next
    Application.CutCopyMode = False
    ActiveWorkbook.Close False
End Sub