VBA Macro For Merging Worksheets

 VBA Macro For Merging Worksheets

Here is a three-step guide to combine all the data of all the sheets to single sheet. Follow these steps as mention: -

1.  Press ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window or you can open Microsoft Visual Basic window from developer tab >>Macros (in Code Group) as shown picture. (step by step guide about to add developer tab Click here).

2. Copy the following VBA and Click Insert > Module, and paste the following code in the Module Window.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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

3. Now press F5 key to run this code, and it’s all done. A new worksheet named Combined has been created in which all the data from all the worksheets in the workbook has been merged and this worksheet will add before all worksheets.

Notes: -

• Your data must start from A1, if not, the code will not take effect.
• Your data must have the same structure.
• This code only can combine all worksheets of the active workbook, if you want to merge worksheets from multiple workbooks, <<< click here >>>

• Tweet
• Share
• Share
• Share
• Share