Automatically combining multiple excels into one

[marmoor99]

Hi All

I have multiple excels being uploaded to a shared folder every week and I'd like to create a master excel sheet that whenever someone drops an excel into this folder it
automatically appends it to the master file all the excels are formatted as table and all have identical columns

Thanks

[Richard Buttrey]

Hi,

Here's the general approach I use.

Sub Open_All_Files()
    Dim oWbk As Workbook, wb as Workbook
    Dim sFil As String
    Dim sPath As String
    
    Set wb = Thisworkbook
    sPath = "C:\your path\"    'location of files
    ChDir sPath
    sFil = Dir("*.xlsx")    'change or add formats
    Do While sFil <> ""    'will start LOOP until all files in folder sPath have been looped through 
    Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file

        ' do something
        oWbk.Close True    'close the workbook, saving changes
        sFil = Dir
    Loop    ' End of LOOP
End Sub

The do something would be something like

Sheet1.Range("A1:E100").Copy
wb.Activate
Sheet1.Range("A" & Rows.Count).End(xlup).Cells(2,1).PasteSPecial(xlPasteValues)

[marmoor99]

Hi,

Here's the general approach I use.

Sub Open_All_Files()
    Dim oWbk As Workbook, wb as Workbook
    Dim sFil As String
    Dim sPath As String
    
    Set wb = Thisworkbook
    sPath = "C:\your path\"    'location of files
    ChDir sPath
    sFil = Dir("*.xlsx")    'change or add formats
    Do While sFil <> ""    'will start LOOP until all files in folder sPath have been looped through 
    Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file

        ' do something
        oWbk.Close True    'close the workbook, saving changes
        sFil = Dir
    Loop    ' End of LOOP
End Sub

The do something would be something like

Sheet1.Range("A1:E100").Copy
wb.Activate
Sheet1.Range("A" & Rows.Count).End(xlup).Cells(2,1).PasteSPecial(xlPasteValues)

Hi Richard. Thanks for the quick repaly. Unfortunatly I have no experiance in in scripting so I dont have a clue how to implement this...is there a way to thus with power query or any other microsoft tool? Seems like something very basic to me...

[Richard Buttrey]

Hi Richard. Thanks for the quick repaly. Unfortunatly I have no experiance in in scripting so I dont have a clue how to implement this...is there a way to thus with power query or any other microsoft tool? Seems like something very basic to me...

There may be I'm not familar with exactly how, but it may involve some level of macro scripting to automate it.

How many workbooks are you talking about? If you upload a couple of examples and summary workbook that contains what you want to see as a result I'll add the macro for you.