Subscribe to our YouTube Channel for more Videos

How to merge worksheets to one worksheet in Microsoft Excel 2016?


There are times when you might need to merge different worksheets in to a single worksheet in Microsoft Excel. You can use the VBA code to perform this in Microsoft Excel 2016.

How to merge worksheets to one worksheet in Microsoft Excel 2016?

Assume that you have a workbook with 2 worksheets (sheet2 , sheet3) which contains the name of the users in the cells f rom B3 and you are interested in merging it into a single worksheet.

imageimage

 

 

 

 

 

 

 

1. First step for us would be to open the Microsoft Visual Basic for Application Window. You can use the “Alt +F11” shortcut key in Excel to open this window.

image

2. Click the Insert menu and select Module from it.

image

3. With the Module screen loaded , paste the following code to it.

Sub Merge()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Merged Sheet"
Sheets(2).Activate
Range("B3").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("B3")
For i = 2 To Sheets.Count
Sheets(i).Activate
Range("B3").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(2)
Next
End Sub


4. Press F5 shortcut key to run the code. This will create a new worksheet called “Merged Sheet” and combine all the data from the sheet2  and sheet3.

image

Senthil Kumar B
Senthil Kumar is a former Microsoft MVP (Most Valuable Professional). He is a Co-Author of the book "Windows 10 Development Recipes using JavaScript and CSS" for Apress Publication. He is a technical presenter, blogger, mentor and a Geek.  Senthil is a regular speaker is various local user groups. He has presented at conferences like Great Indian Developer Summit (GIDS) & Microsoft DevCamps. You can reach out to him via his Twitter handle @isenthil.