When the possession of several files with the same tabs (e.g., Excel sheets) need to be merged into one arises, there are several helpful techniques to use. Here’s an easy style that you can adopt:
Method 1: Using Excell
Launch Excel: As the first step, launch a new Excel file.
Import with Power Query:
Click on the Data tab, then select Get Data – From File – From Workbook.
Locate the first file you want to join together and click on it.
In the Navigator area, pick the desired tab to import and choose Load. Do this for every file that is to be joined together.
Consolidate the information:
After importing data from every file, click on the Home tab and click on Append Queries to merge the data into one table.
This process can be done for more tabs or files.
Finishing Up: After merging, you can either arrange the information, eliminate repetitions, or edit any other columns if necessary.
Close Your Workbook: When you are done, go ahead and put your file containing the merged data into a record.
Method 2: Using VBA in Excel
Where the number of files is great, and there is a need for automation, one can use a simple VBA macro:
Launch Excel and Press Alt + F11. This enables the user to access the VBA window, among other things.
Open a New Module: On any item in the Project Explorer, right-click, go to Insert, and then Module.
Copy and Paste the Following Code:
Sub CombineSheets()
Dim wb As Workbook
Dim ws As Worksheet
Dim combinedWs As Worksheet
Dim lastRow As Long
Dim folderPath As String
Dim fileName As String
folderPath = "C:\Path\To\Your\Files\" ' Change this to your folder path
fileName = Dir(folderPath & "*.xlsx")
Set combinedWs = ThisWorkbook.Sheets(1) ' Assuming the first sheet is where you want to combine data
Do While fileName <> ""
Set wb = Workbooks.Open(folderPath & fileName)
Set ws = wb.Sheets(1) ' Change the number if you need a different sheet
lastRow = combinedWs.Cells(combinedWs.Rows.Count, 1).End(xlUp).Row + 1
ws.UsedRange.Copy combinedWs.Cells(lastRow, 1)
wb.Close False
fileName = Dir
Loop
End Sub
- Alter the Directory and Number of the Sheet: Please ensure that you modify the column path to the location of your files and edit the sheet number if necessary.
- Execute the Macro: Now, you should be able to close the VBA window and go back to Excel. You can run the macro by clicking Alt + F8, selecting CombineSheets, and pressing Run.
