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

Leave a Reply