Blog

How to Combine Multiple Sheets to Single Sheet in Excel

How to Combine Multiple Sheets to Single Sheet in Excel




                         When we use Excel, we usually need to merge multiple worksheets into a single master worksheet, so that we can analyze and count the data quickly and easily or create Pivot Table for analyzing the data in a useful way. For general users, we couldn’t finish it except using copy and paste command, it is easy to combine few worksheets, but if there are numerous worksheets, this method will be time-consuming.For sorting this issue we can use the Macro Code for doing this work in a minute.Only think we need to take care the header column of the data should be same.

 

Eg.

Here I have the data of the Mobile Distributor and Data is provided for the month wise and I need to combine all the data into the single sheet.


 


 

Step-1

Copy the macro code from the file (download the file from the below link)

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

Step-2

Paste the code in the Excel VB Editor by Typing ALT+F11 and insert the new module and return back to Excel by clicking the excel icon below the file tab.

 

 

 

 

 

Step-3

 

Click on the View Tab and click on the Macro and run the macro named “Combine” and name of our code is combine, and now the combined sheet will automatically generate with data from all sheets.



  

 Click here for the VB Code File

Click here for Sample Work Book


 

 

Video Link: Watch Video

Phone : +91 8606648045
Email : info@excelskillcorner.com
Excel Skill Corner
Kerala, India.