The disadvantage: This method is very troublesome if you have to deal with several worksheets or cell ranges.On the other hand: For just a few ranges it’s probably the fastest way.The “FROM” portion tells Microsoft Query where to find the data.
Easily Combine Multiple Workbooks Into One With Only Several Clicks The Combine Workbooks utility of Kutools for Excel helps you quickly combine certain workbooks into one. Besides, with this feature, you can combine multiple worksheets or same name worksheets across workbooks into one, or consolidate values across workbooks into one sheet based on your needs.
If there are just a couple of workbooks need to be combined, you can use the Move or Copy command to manually move or copy worksheets from the original workbook to the master workbook. Open the workbooks which you will merge into a master workbook. Select the worksheets in the original workbook that you will move or copy to the master workbook. After selecting the needed worksheets, right click the sheet tab, and then click Move or Copy from the context menu. Then the Move or Copy dialog pops up, in the To book drop-down, select the master workbook you will move or copy worksheets into.
After adding the Excel files, click the Finish button and choose a folder to save the master workbook.
Then a dialog pops to remind you that all combined workbooks should be saved and the feature can't be applied to protected workbooks, please click the OK button. In the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into one workbook option, and then click the Next button. In the Combine Worksheets - Step 2 of 3 dialog box, click the Add File or Folder to add the Excel files you will merge into one.
Please repeat the above steps to move worksheets from other workbooks into the master workbook. Put all workbooks that you want to combine into one under the same directory. Launch an Excel file (this workbook will be the master workbook). Press the Alt F11 keys to open the Microsoft Visual Basic for applications window. In VBA codes, “Sub Merge Workbooks() 'Updated by Extendoffice 2019/2/20 Dim x Str Path As String Dim x Str FName As String Dim x WS As Worksheet Dim x MWS As Worksheet Dim x TWB As Workbook Dim x Str AWBName As String On Error Resume Next x Str Path = "C:\Users\DT168\Desktop\KTE\" x Str FName = Dir(x Str Path & "*.xlsx") Application. Display Alerts = False Set x TWB = This Workbook Do While Len(x Str FName) 0 Workbooks.