HomeMicrosoft OfficeExcelHow to merge worksheets to one worksheet in Microsoft Excel 2016?

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

    2 Comments

  1. Steve
    July 5, 2018
    Reply

    Hi,

    I tried this VBA command but it seems to only bring up one of the information from 1 worksheet in the merged worksheet rather than all the worksheets, would you be able to assist?

    Thanks,

    Steve

  2. Anne Wagoner
    March 11, 2020
    Reply

    you need to select the sheets you want to merge prior to running the macro

Leave a Reply

You May Also Like

In this article, you will learn about the SKEW.P function, the formula syntax and usage of the function in Microsoft...
In this article, you will learn about the SKEW function, the formula syntax and usage of the function in Microsoft...
In this article, you will learn about the RANK.EQ function, the formula syntax and usage of the function in Microsoft...
  • Excel
  • November 23, 2021