Do you need to quickly copy multiple tables to one table in Excel for a bird’s eye view of the data? You’ve come to the right place. Keep reading!

One way to get insights from chunks of data in an Excel workbook is by combining all the datasets or tables in one worksheet. Then, apply your data analysis formulas or create charts to visualize the story your data has to say.

You could browse through all the worksheets of your Excel workbook to find data tables or datasets. Then, copy these one by one in a new worksheet. If the workbook contains hundreds of sheets and each sheet contains multiple tables, then it could take months to create a worksheet containing all the tables.

Here are cool features of Excel like functions, Excel VBA, Power Query, and Office Scripts.

In this tutorial, you’ll explore multiple ways to combine tables in one worksheet for a side-by-side comparison of data or aggregated data analysis. The methods use the above Excel tools to automate the process eliminating human error caused by repetitive copy-pasting of Excel data.

Why Should You Copy Multiple Tables to One Table in Excel

Find below the reasons behind copying multiple tables to one table in Excel:

1. It combines data from different tables into a single table for easy analysis and reporting.
2. The process simplifies data management by having all related information in one location.
3. This helps you to avoid the need to switch between multiple tables, saving time and effort.
4. It enables powerful Excel functions and formulas to work with consolidated data.
5. The process helps you to create dynamic reports and dashboards with a single data source.
6. It facilitates data sorting, filtering, and searching across multiple datasets.
7. You can also enhance the data visualization and charting capabilities by using consolidated data
8. The process simplifies collaboration among team members by sharing a single, unified dataset.

Reasons to Use Excel VBA, Office Scripts, and Power Query to Copy Multiple Tables to One Table

Here are the benefits of using Excel VBA, Power Query, and Office Scripts to merge multiple tables into one table over manual functions like VLOOKUP, INDEX and MATCH, VSTACK, and HSTACK:

1. These tools enable you to automate the table merging process so you can use less staff to manage the data entry and analysis processes in your business.
2. You can avoid human error and compliance risks by automating the whole table merging process.
3. You can set specific rules for combining tables and the above tools will always deliver the same data organization. Therefore, you standardize the look, structure, and integrity of the table-merging process.
4. These tools also come with heavy customizations so you can fit any business scenario.
5. You and your data analysts can focus more on the actual data analysis process while Excel does all the data organization and management process in the backend.
6. You can create templates from the current project and reuse the template in future projects.

Now that you know the advantages of copying multiple tables to one in Excel and automating the process using Office Scripts, Power Query, and Excel VBA, it’s time to explore the actual methods below.

VSTACK & HSTACK Functions to Combine Tables

VSTACK and HSTACK formulas in Excel also allow you to combine multiple table data in one table without using any complicated steps. However, automation opportunities are lesser compared to other methods mentioned in this article.

Vertically Combine Tables Using VSTACK

Find below the steps to merge tables in the same workbook:

1. Go to a worksheet where you want the merged table.
2. Highlight a cell and enter the following formula:

=VSTACK(Table1[#All],Table2[#All],Table3[#All])

3. Hit Enter.

4. You’ll see a stacked table of all the above in the destination worksheet instantly.

To customize the above formula, replace Table1[#All] with the table name of your workbook, then replace the next one, Table2[#All] and so on.

If you want to get headers of the first table only, try this formula instead:

=VSTACK(Table1[#Headers],Table1,Table2,Table3)

The above image is a reflection of using the alternative VSTACK function in Excel.

Horizontally Combine Tables Using HSTACK

Here’s how you can merge the tables horizontally:

1. Highlight a cell and enter this formula:

=HSTACK(Table1[#All],Table2[#All],Table3[#All])

2. Hit Enter to get horizontal combined tables from the referred sources.

To make these formulas work on your own workbook, simply replace Table1[#All], Table2[#All], etc., with the actual table names in your workbook.

You can also add more arrays by adding an Excel separator (comma) at the end of the last array reference and entering the new array reference.

Use Excel VBA to Copy Many Tables to One Vertically

You can stack tables from multiple sheets in a workbook in one worksheet using this Excel VBA script. This is suitable to analyze sales or performance data from different months in one large table.

For instance, I got sales data for Jan, Feb, and Mar in three different sheets in a workbook. Suppose, I need to understand the first quarter sales performance, I’d need all monthly sales tables in one large table.

Instead of copying the tables one by one, I can use a VBA script to eliminate human error and repetitive tasks. Find below the steps you can also use to accomplish the same:

1. Press Alt + F11 to call the Excel VBA Editor tool.
2. Click the Insert button and choose Module.
3. In the newly created module, copy and paste this VBA script:

Sub StackTablesVertically()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim lastRow As Long
    Dim destLastRow As Long
    Dim rngSource As Range
    Dim rngDestination As Range
    
    Set wsDestination = ThisWorkbook.Sheets("Sheet4")
    
    wsDestination.UsedRange.Clear
    
    For Each wsSource In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        Set rngSource = wsSource.Range("A2:D9")
        
        destLastRow = wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row + 1
        
        Set rngDestination = wsDestination.Range("A" & destLastRow)
        
        rngSource.Copy rngDestination
        
        destLastRow = destLastRow + rngSource.Rows.Count
    Next wsSource
End Sub

4. Click the Save button and close the Excel VBA Editor interface.

5. Create a new sheet in your workbook and name it Sheet4.

6. Now, call the Macro dialog box by pressing the Alt + F8 keys.
7. There, choose the StackTablesVertically macro and click the Run button.
8. Go to Sheet4 and you should find all the sales data tables stacked from Sheet1 to Sheet3.

Here’s how you can modify the above VBA script to make it work for your own dataset:

• Replace "Sheet4" with the destination sheet name like "Sheet5", "QuarterlySales", etc.
• If you need to copy tables from more sheets, add the sheet names in this code element (Array("Sheet1", "Sheet2", "Sheet3")). For instance, (Array("Sheet1", "Sheet2", "Sheet3", "Sheet5", "Sheet6")).
• The code element "A2:D9" says Excel which cell range to be copied from each sheet. If the data table in your worksheets is larger than "A2:D9", adjust the cell range accordingly.

The above VBA script only copies the tables from the same cell range, which is "A2:D9". If you need to copy tables from different cell ranges in different worksheets, use the following code:

Sub StackTablesVertically()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim lastRow As Long
    Dim destLastRow As Long
    Dim rngSource As Range
    Dim rngDestination As Range
    Dim tableRange As Range
    
    Set wsDestination = ThisWorkbook.Sheets("Sheet5")
    
    wsDestination.UsedRange.Clear
    
    For Each wsSource In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        Select Case wsSource.Name
            Case "Sheet1"
                Set tableRange = wsSource.Range("A2:D9")
            Case "Sheet2"
                Set tableRange = wsSource.Range("B2:C9")
            Case "Sheet3"
                Set tableRange = wsSource.Range("C2:D9")
            'Add more cases for other sheets if needed
            'Case "Sheet4"
            '   Set tableRange = wsSource.Range("...")
            '...
            Case Else
                'If the sheet is not included in the list, skip it
                GoTo ContinueLoop
        End Select
        
        destLastRow = wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row + 1
        Set rngDestination = wsDestination.Range("A" & destLastRow)
        
        tableRange.Copy rngDestination
        
        destLastRow = destLastRow + tableRange.Rows.Count
        
ContinueLoop:
    Next wsSource
End Sub

Use Excel VBA to Copy Many Tables to One Horizontally

If you need to visualize data tables side by side for a better understanding of performance, sales, or any other data, you can stack those horizontally in one worksheet by copying from different worksheets. Find below the VBA code to automate this task:

Sub StackTablesHorizontally()
    Dim mainSheet As Worksheet, sourceSheet As Worksheet
    Dim mainRange As Range, sourceRange As Range
    Dim lastCol As Long, newRow As Long
    Dim sheetNames As Variant
    Dim i As Long
    
    Set mainSheet = ThisWorkbook.Sheets("Sheet6")
    Set mainRange = mainSheet.Range("A2")
    
    sheetNames = Array("Sheet1", "Sheet2", "Sheet3")
    
    For i = 0 To UBound(sheetNames)

        If Evaluate("ISREF('" & sheetNames(i) & "'!A1)") Then

            Set sourceSheet = ThisWorkbook.Sheets(sheetNames(i))
            Set sourceRange = sourceSheet.Range("A2:D9")
            sourceRange.Copy mainRange
            
            lastCol = mainSheet.Cells(2, mainSheet.Columns.Count).End(xlToLeft).Column + 1
            
            Set mainRange = mainSheet.Cells(2, lastCol)
        End If
    Next i
    
    Application.CutCopyMode = False
    
    mainSheet.Cells.EntireColumn.AutoFit
End Sub

The procedure to run the VBA script on your Excel workbook will remain the same as explained earlier. Find below the instructions to customize the script to fit your own datasets:

• "Sheet6" is the destination worksheet. Change it according to your workbook.
• Range("A2") tells Excel to start copying tables from the A2 cell. If you want to start it in a different cell, enter that here.
• If your workbook has more sheets than the ones stated in the code, add those in this code element: Array("Sheet1", "Sheet2", "Sheet3"). For example Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5" ).

Use Office Scripts to Copy Multiple Tables to One Table in Excel

Do you need to copy multiple tables into a large table on the Excel online application using automation? You can use Office Scripts.

You can access this feature on your Excel on the web app if you own a Microsoft 365 Business Standard or a better subscription plan. The feature is also available on the Excel for Microsoft 365 desktop app when connected to the internet.

Head over to your Excel online or desktop worksheet and see if you’ve got the Automate tab on the ribbon menu. If you do, you can follow these steps:

1. Click the Automate tab and go to the Scripting Tools commands block.
2. There, click on the New Script button.
3. Now, copy and paste the following Office Scripts code into the Code Editor panel:

function main(workbook: ExcelScript.Workbook) {
	// Add a new worksheet with name "Sheet4"
	let sheet4 = workbook.addWorksheet("Sheet4");
	// Set range A1 on sheet4
	sheet4.getRange("A1").setFormulaLocal("=VSTACK(Table1[#Headers],Table1,Table2,Table3)");
}

4. Click the Save script button to save the code for future use.
5. Hit the Run button to execute the code.

Head over to Sheet4 in your workbook and you should see that Excel copied all the source tables under the header columns of the table of Sheet1.

If you need header columns of the individual tables from the source worksheets, use this code instead:

function main(workbook: ExcelScript.Workbook) {
	// Add a new worksheet with name "Sheet4"
	let sheet4 = workbook.addWorksheet("Sheet4");
	// Set range A1 on sheet4
	sheet4.getRange("A1").setFormulaLocal("=VSTACK(Table1[#All],Table2[#All],Table3[#All])");
}

Need to copy multiple tables in one Excel worksheet side-by-side using Office Scripts? Yes, you can do that too. Find below the script you can use:

function main(workbook: ExcelScript.Workbook) {
	// Add a new worksheet with name "Sheet4"
	let sheet4 = workbook.addWorksheet("Sheet4");
	// Set range A1 on sheet4
	sheet4.getRange("A1").setFormulaLocal("=HSTACK(Table1[#All],Table2[#All],Table3[#All])");
}

Use Power Query to Copy Multiple Tables to One Table

When importing tables from an external data source or organizing tables in a local Excel workbook, you can use the Power Query tool to merge tables. This method comes with a visual interface so you can see which tables you’re merging.

Append Tables in Power Query

The Append Queries function of Power Query lets you copy multiple tables into one table horizontally. Here are the detailed steps so you can follow along:

1. Click the Data tab and go to the Get & Transform Data block.
2. There, click Get Data and hover the mouse over From File.
3. In the overflow menu, choose From Excel Workbook.

4. Navigate to the source Excel workbook on the Import Data dialog box, select it, and click Import.

5. On the Power Query Navigator wizard, checkmark the Select multiple items checkbox.
6. Choose all the tables you want to copy side-by-side.
7. Click Transform Data to open the tables in Power Query Editor.

8. Hit the Combine button on the Power Query Home tab and choose Append Queries.

9. On the Append wizard, select Three or more tables.
10. Add all the tables to the right-side column and click OK.

11. Click Close & Load to insert the appended table into a new table in your workbook.

Merge Tables in Power Query

Suppose, you want to visualize all the tables side-by-side, here’s how you should proceed on Power Query:

1. Follow steps 1 through 7 as mentioned above to open all the tables in Power Query Editor.
2. Now, click the Combine button and choose Merge Queries.

3. On the Merge wizard, select Table 2.
4. Click on the same column (Item) on both the table.
5. Click OK.

6. Hit the two-sided arrow icon on the newly added table and click OK.

7. Again, click Merge Queries and add Table 3 to Table 1.

8. Click the two-way arrow of Table 3 and click OK to include all of its columns.
9. Click Close and Load to import the merged tables as a new table in your workbook.

Conclusions

You have learned three different Excel tools to copy multiple tables to one table in Excel. These are Excel VBA, Office Scripts, and Power Query.

Try out the method that fits your Excel workbook and project. Don’t forget to comment below to express your experience with the methods.