How to Merge Excel Worksheets Without Copying and Pasting?

In this article, we will demonstrate three ways to merge Excel worksheets without copying and pasting. In the first method, we combine data from multiple sheets into one sheet. In the second approach, we use the Power Query tool of Excel. Lastly, we apply a VBA code to merge multiple Excel worksheets.

To demonstrate all the procedures, we’ll use the following two datasets in two different sheets:

Method 1 – Consolidating Data

Suppose we have two worksheets with the same product name. The number of sales in three months is given and we want to find the total amount of sales of each product in each month. We can accomplish this using the Consolidate feature.

Steps:

• In an empty worksheet in the same workbook, select a cell to display the result, here cell B4.
• Go to the Data tab and choose Consolidate in the Data Tools group.
  

The Consolidate dialog box opens.

• From the Function drop-down, select Sum.
• In the Reference type box, enter the data range from the first dataset.
• Mark both options under Use labels in option.
• Click Add.

• In the Reference type box, insert the data range of the second dataset.
• Click Add.
• Click OK.

Some data is inserted in your new worksheet that contains different product names with the monthly sales. In this worksheet, the monthly sales amount is summed.

In the new worksheet, the Product Name heading is missing, because it was in cell B4 in both of the worksheets, and in the Consolidate feature this cell is taken as the reference cell. So, after performing the Consolidate process, the reference cell text is not displayed.

• Format the new worksheet with the newly added data.

Read More: Data Validation and Consolidation in Excel 

Method 2 – Using Power Query Tool

With the help of Power Query, it is quite easy to combine multiple data from different worksheets into a single worksheet. Also, using this feature we can merge two Excel sheets based on the same column.

Steps:

First we need to transform both data sets into tables.

• Select the data range B4:E8 of the first dataset.
• Go to the Insert tab and select Table.

The Create Table dialog box opens.

• Enter the proper data range and click OK.

The data range will convert into an Excel table.

• Transform the second dataset into a table by following the above steps.

• For merging, open a new worksheet and go to the Data tab of the ribbon.
• From the Get & Transform Data group, select Get Data.

• From the previous command, select From File.
• Choose From Excel Workbook to load all the worksheets for merging.

• Browse to the location of the workbook that contains the merged sheets.
• Choose the workbook and select Import.

The Navigator dialog box opens.

• Mark the option Select multiple items for merging.
• Select all the tables from the workbook that you want to merge.
• Click Load.

The Queries & Connections window opens.

• Go to the Get Data command from the Data tab.
• From the command dropdown, select Combine Queries.
• From the second dropdown, choose Merge.

The Merge window opens for selecting the sheets.

• Select the tables to merge.
• Select the column that is the same for both tables.
• Click OK.

The loaded data sets are merged together, but the second table is not fully expanded.

• To expand the table, click on the icon in the upper right corner of the heading.

A new dialog box appears.

• Deselect the header of the column that will be the same in the new worksheet.
• Deselect Use original column name as prefix.
• Click OK.

The newly merged sheet is loaded in the Power Query.

• To transfer it into the worksheet, choose Close & Load.

The data from the merged worksheets appears in the specified location.

• To append the worksheets (merging the worksheets based on rows), after converting both the datasets into tables, open a new worksheet.
• Go to the Get Data command from the Insert tab and select Combine Queries.

The Append window opens.

• Select the table number to append.
• Select all the tables according to their serials.
• Click OK.

The Power Query will append both the tables from the worksheets into a single one.

• Select Close & Load to transfer this merged table into the main worksheet.

The final result will look like the following image.

Read More: How to Combine Multiple Worksheets into One Workbook?

Method 3 – Using VBA Code

Now let’s use VBA code for merging multiple worksheets into one without copying and pasting. We will insert the needed sequence and command in the code, and after running, it will do the merging automatically.

Steps:

• Open a new worksheet and go to the Developer tab of the ribbon.
• From the Code group, select Visual Basic.

The VBA Editor window opens.

• From the Insert tab, select Module.

• Copy the following code and paste it into the module:

'Giving name of sub procedure
Sub Merge_Excel_Worksheets()
'Declaring variables
Dim x As Integer
'Enabling error handling
On Error Resume Next
'Seleting a new worksheet for showing result
Sheets(1).Select
Worksheets.Add
'Naming the new workhseet
Sheets(1).Name = "Merged"
'Selecting first sheet for merging
Sheets(2).Activate
'Addressing the cell range
Range("B4:E8").EntireRow.Select
'Selecting the destination for new cell range
Selection.Copy Destination:=Sheets(1).Range("B4:E8")
'Starting the for loop
For x = 2 To Sheets.Count
'Activating x number of sheets for merging
Sheets(x).Activate
'Selecting the range for merging
Range("B4:E8").Select
'Selecting the current region
Selection.CurrentRegion.Select
'Selecting the tables without selecting the headers
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
'Merging all data in the new sheet
Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(5)
Next
End Sub

• Save the code.

Sub Merge_Excel_Worksheets()

Dim x As Integer

On Error Resume Next

Sheets(1).Select
Worksheets.Add

Sheets(1).Name = "Merged"

Sheets(2).Activate

Range("B4:E8").EntireRow.Select

Selection.Copy Destination:=Sheets(1).Range("B4:E8")

For x = 2 To Sheets.Count

Sheets(x).Activate
Range("B4:E8").Select

Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(5)

• Press F5 or the Run button to run the code.
• Before running, keep the cursor in the module.

After running this code, a new worksheet with all the merged data in raw format is created.

• Add the headers and format the sheet as desired.

How to Use Power Query to Merge Multiple Excel Files from Folder

In the above example, we demonstrated the procedure to merge Excel worksheets without copying and pasting them. Now let’s merge multiple Excel files from a folder using Power Query.

Steps:

• Take the workbook below containing the sales data for January.
• Select the data range B4:C18 and click on the Insert tab on the ribbon.
• From the Tables group, select Table.

• In the Create Table dialog box, after checking all the criteria, click OK.

The dataset is converted into a tabular format.

• Save this workbook in a specific folder.

• Like in the following image, store all the workbooks that you will merge in the same folder, and convert all their data ranges into the tabular format following the previous steps.

• Open a new worksheet, and from the Data tab, select Get Data.
• From the drop-down, choose From File, and then From Folder.

A new window containing all the workbooks opens.

• Select Combine & Transform Data.

The Combine Files window opens. The sample files and the dataset are loaded.

• Set the Parameter to Table instead of the sheet name.
• Click OK.

All the workbooks are merged into one.

• To transfer it into the new worksheet, select Close & Load.

• The new worksheet with the merged workbooks looks like the following image:

How to Use CMD to Merge Excel Files Into One

We can also merge multiple Excel files into one using CMD. To do that, we’ll have to transform our workbook from .xlsx format to .csv format.

Steps:

• Take the same three workbooks as in the previous discussion.
• From any of the workbooks, go to the File tab on the ribbon.
  

• From the Home window, select Save As and then Browse.

• Create a folder in the C: drive on your PC as the saving destination for these workbooks.
• For Save as type, select CSV as the format.
• Click Save to save the file.

All the workbooks are placed in the specified folder.

• Copy the path of the folder Sales File CSV in which we have stored our Excel files in CSV.
• To view the Copy path option, expand the ribbon by clicking on the dropdown symbol in the top right corner.
• Select the folder Sales File CSV and click on the Copy path option.

• Press the Windows key + R together to open the Run wizard.
• In the Open input box, press cmd and click OK.

The command prompt opens.

• Enter cd then a space, then paste the path of the copied folder.
• Press Enter.

After pressing Enter, the directory from the previous line opens.

• Enter copy *.csv Merged.csv.

You must insert a space after the word copy. We chose Merged as the new file name.

• After pressing Enter, this code will automatically merge all the workbooks.

• Return to the folder where all the CSV files are saved, and observe the newly merged file.

• Open the file to find all the data in the raw format.

• Scroll down to see all the data.

• Save this file in the .xlsx format.

• Open the Excel file and format the sheet.

• Scroll down to see the whole sheet in the case of a large dataset.

Download Practice Workbook

<< Go Back To Merge Sheets in Excel | Merge in Excel | Learn Excel