How to Merge Excel Worksheets Without Copying and Pasting?

The problem that occurs while copying different elements of a worksheet into another worksheet is that you may not get the actual formatting of the elements that you require. Copying and pasting a large number of elements into a worksheet in Microsoft Excel can take a lot of time as well. We will get to know the process of how Power Query combines multiple Excel files and how to consolidate data from multiple worksheets into a single worksheet. In this article, I will show you how to merge Excel worksheets without copying and pasting.

How to Merge Excel Worksheets Without Copying and Pasting: 3 Easy Ways

In this article, you will see three easy ways to merge Excel worksheets without copying and pasting. In the first method, I will combine data from multiple sheets into one sheet. In my second approach, I will take the help of the Power Query tool of Excel. Lastly, I will apply a VBA code to merge multiple Excel worksheets.

To show all the procedures, I will use the following two data sets in two different sheets.

1. Consolidating Data from Multiple Worksheets to Merge Without Copying and Pasting

Suppose you have two worksheets with the same product name. The number of sales in three months is given and you want to find the total amount of sales of each product in each month. That’s when you will need the Consolidate feature of Excel to perform this task. See the following steps to understand this feature.

Step 1:

• First of all, take an empty worksheet in the same workbook and select a cell from where you want to see the result.
• Then, go to the Data tab of the ribbon and choose Consolidate, which is under the Data Tools group.
  

Step 2:

• Secondly, you will see the Consolidate dialog box.
• Here, from the Function drop-down select Sum.
• Then, in the Reference type box, enter the data range from the first data set.
• Thirdly, mark both options under the Use labels in the header.
• Lastly, press Add.

Step 3:

• Thirdly, again in the Reference type box, insert the data range of the second data set.
• After insertion, press Add.
• Lastly, click OK.

Step 4:

• Fourthly, after pressing OK. You will see that 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 added together.

Step 5:

• Fifthly, in the new worksheet, you will see the Product Name heading is missing.
• That’s because it was in the B4 cell in both of the worksheets and in the Consolidate feature, this cell is taken as the reference cell.
• So, after you perform the Consolidate feature, the reference cell text is not displayed.
• Consequently, format the new worksheet with the newly added data.

Read More: Data Validation and Consolidation in Excel 

2. Utilizing Power Query Tool to Merge Excel Worksheets

Excel introduces us to its new feature, Power Query which is capable of doing multiple things that couldn’t be used in the previous versions. 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, you can merge two Excel sheets based on the same column. So, to learn more about this procedure, follow the below-given steps.

Step 1:

• First of all, I will transform both data sets into tables.
• For that, select the data range B4:E8 of the first data set.
• Then, go to the Insert tab of the ribbon, and from there select Table.

Step 2:

• Secondly, you will see the Create Table dialog box.
• Here, input the proper data range and then select OK.

Step 3:

• Thirdly, you will see that your data range will convert into an Excel table after the previous steps.
• Moreover, transform the second data set into a table by following the above steps.

Step 4:

• Fourthly, for merging, open a new worksheet and go to the Data tab of the ribbon.
• Then, from the Get & Transform Data group, select Get Data.

Step 5:

• Fifthly, from the previous command, select From File.
• Then, choose From Excel Workbook to load all the worksheets for merging.

Step 6:

• After that, browse to the location where the workbook is placed that contains the sheets from merging.
• Then, choose the workbook and select Import.

Step 7:

• Then, after importing the workbook, you will see the Navigator dialog box.
• Here, mark the option. Select multiple items for merging.
• Then, select all the tables from the workbook that you want to merge.
• Lastly, press Load.

Step 8:

• After that, you will see the Queries & Connections window pane after loading the tables.

Step 9:

• Afterward, again go to the Get Data command from the Data tab of the ribbon.
• This time, from the command dropdown, select Combine Queries.
• From the second dropdown, choose Merge.

Step 10:

• Here, you will see the Merge window for selecting the sheets.
• From there, first, select the tables that you want to merge.
• Then, select the column that is the same for both tables.
• Lastly, press OK.

Step 11:

• Afterward, you will see the loaded data sets merged together.
• Here, the second table is not fully expanded.
• To expand the table, click on the icon that is in the upper right corner of the heading.

Step 12:

• From there, a new dialog box will appear.
• Here, deselect the header of the column that you want to be the same in the new worksheet.
• Then, deselect the mark from Use original column name as prefix.
• Lastly, press OK.

Step 13:

• After that, you will see the newly merged sheet in the Power Query.
• To transfer it into the worksheet, choose Close & Load.

Step 14:

• Finally, you will see the data from the merged worksheets in one based on the same column.

Step 15:

• Additionally, if you want to append the worksheets, that means merging the worksheets based on rows, then after converting both the data sets into tables, open a new worksheet.
• Then, again go to the Get Data command from the Insert tab and select Combine Queries.

Step 16:

• Here, you will see the Append window.
• Firstly, select the table number that you want to append.
• Then, select all the tables according to their serials.
• Lastly, press OK.

Step 17:

• After that, the Power Query will append both the tables from the worksheets into a single one.
• Here, select Close & Load to transfer this merged table into the main worksheet.

Step 18:

• Consequently, the final result will look like the following image.

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

3. Applying VBA Code to Merge Excel Worksheets Without Copying and Pasting

In this last procedure, I will use VBA code for merging multiple worksheets into one without copying and pasting. For that, I won’t need any features or tools from Excel. I will insert the needed sequence and command in the code, and after running, it will do the merging automatically. Go through the following steps for a better understanding.

Step 1:

• Firstly, open a new worksheet and then go to the Developer tab of the ribbon.
• Then, from the Code group, select Visual Basic.

Step 2:

• Secondly, you will see the VBA window after selecting the previous command.
• Then, from the Insert tab, select Module.

Step 3:

• Thirdly, copy the following code and paste it into the module.
• After copying, save the code.

'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

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)

Step 4:

• Fourthly, press F5 or the Run button to run the code for performing the task.
• Before pressing, keep the cursor in the module.

Step 5:

• Finally, after running this code, you will see a new worksheet with all the merged data in raw format.

Step 6:

• Consequently, add the headers and format the sheet as per your choice.

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

In the above discussion, you saw the procedure to merge Excel worksheets without copying and pasting them into one. Now, I will demonstrate how to merge multiple Excel files from a folder. I will again take the help of the Power Query of Excel for that. See the below-given steps for a better understanding.

Step 1:

• Firstly, take the following workbook containing the sales data for January.
• Then, select the data range B4:C18 and click on the Insert tab of the ribbon.
• After that, from the Tables group, select Table.

Step 2:

• Secondly, in the Create Table dialog box, after checking all the criteria, select OK.

Step 3:

• Consequently, this command will turn the data set into a tabular format.
• Then, save this workbook in a specific folder.

Step 4:

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

Step 5:

• Fifthly, open a new worksheet, and from the Data tab, select Get Data.
• Then, from the drop-down choose From File, and after that select From Folder.

Step 6:

• Sixthly, you will see a new window containing all the workbooks.
• From there, select Combine & Transform Data.

Step 7:

• Afterward, you will see the Combine Files window.
• Here, see the sample files and the data set along with it.
• While setting the Parameter, select Table instead of the sheet name.
• Lastly, press OK.

Step 8:

• Afterward, you will see all the workbooks have been merged into one.
• To transfer it into the new worksheet, select Close & Load.

Step 9:

• Finally, the new worksheet with the merged workbooks will look like the following image.

How to Use CMD to Merge Excel Files Into One

In the last discussion of this article, I will show you the procedure of merging multiple Excel files into one using CMD. To do that, we have to transform our workbook from .xlsx format to .csv format. See the following steps to do that.

Step 1:

• First of all, take the same three workbooks shown in the previous discussion.
• Then, from any of the workbooks go to the File tab of the ribbon.
  

Step 2:

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

Step 3:

• Thirdly, create a folder in the C: drive on your PC as the saving destination of these workbooks.
• Then, in the saving type, select CSV as the format.
• Lastly, press Save to save the file.

Step 4:

• Fourthly, in the following image, you will see all the workbooks in the specified folder.

Step 5:

• Filthy, we will need to copy the path of the folder Sales File CSV in which we have stored our Excel files in CSV.
• To have the Copy path option, you need to expand the ribbon by clicking on the dropdown symbol in the top right corner.
• Then, select the folder Sales File CSV and click on the Copy path option.

Step 6:

• Afterward, press the Windows key + R altogether to open the Run wizard.
• In the Open type box, press cmd and click OK.

Step 7:

• After that, by entering the previous command, you will open the command prompt.
• Here, type cd give a space, and paste the path of the folder copied in Step 5.
• Then, press Enter.

Step 8:

• After pressing Enter, you will see the directory from the previous line.
• Then type, copy *.csv Merged.csv.
• Here, you must provide space after the copy word.
• Also, I chose Merged as the new file name.

Step 9:

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

Step 10:

• After that, come back to the folder where all the CSV files are saved, and you will see the newly merged files.

Step 11:

• Then open the file and you will find all the data in the raw format.

Step 12:

• As there are data from multiple worksheets, scroll down to see the remaining data.

Step 13:

• Then, again, save this file in the .xlsx format.

Step 14:

• Finally, open the Excel file and format the sheet like the following image.

Step 15:

• Consequently, scroll down to see the whole sheet for a large data set.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to merge Excel worksheets without copying and pasting. Please share any further queries or recommendations with us in the comments section below.

Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.

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