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.
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.
2. Click the Insert menu and select Module from it.
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.
2 Comments
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
you need to select the sheets you want to merge prior to running the macro