Merge Excel Sheets Together

Combine by position Open each source sheet and make sure that your data is in the same position on each sheet. In your destination sheet, click the upper-left cell of the area where you want the consolidated data to appear. On the Data tab, in the Data Tools group, click Consolidate. Nov 11, 2016 This file has 10 sheets, conveniently named Sheet1 through Sheet10. On each sheet, on row 14 there are the column headers: S/N, RegistryNr, Surname, FirstName, (empty), FatherName, MotherName, sex, (empty), DOB. How can I merge every sheet into one dataset? I know that with excelsheets('report.xlsx') I can get all the sheet names into a vector. Enable Excel, and click Kutools Plus Combine, in the Combine Worksheets dialog, check Combine multiple worksheets from workbooks into one worksheet option.

  1. How To Merge Excel Sheets
  2. How To Merge Excel Sheets Together

Sometimes we want to merge multiple sheets into one sheet so that we can easily analyse the data and turn it into some useful information. This articles will tell you how to merge multiple worksheets into one worksheet using VBA.
Example:
Here I have fetched some data from server that returns data into different worksheets. I have added one more sheet and named it as “Master”. Other sheet names doesn’t matter.
Now run this macro.

How to merge sheets using this VBA Macro?

  1. Insert a new sheet and name it “Master” in the workbook. Rename it later if you want.
  2. Insert a module in VBA editor and copy above VBA code.
  3. Run the macro.
  4. You will be asked to select headings. Select the heading and hit OK.

And it is done. All the sheets are merged in master.
How it works?
I assume that you know the basics of object and variable creation in VBA. in the first part we have created object and variables that we will need in our operations.

Well most of the things I have explained using comments in vba code. Let’s look at the main part of this vba code.

In earlier articles we learned how to loop through sheets and how to get last row and column using vba.

Here we are looping through each sheet in main workbook using for loop.
For Each ws In wb.Worksheets

Excel merge sheets in workbook

Then we exclude “master” sheet from looping, since we will be consolidating our data in that sheet.

Then we get last row and last column number.

Now next line is very important. We have done multiple operations into one line.
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range('A' & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)

First we form a range using startRow, startCol and lastRow and lastCol.

This loops runs for all the sheets and copies each sheets data into master sheet.

Finally, in the end of the macro we activate the mastersheet to see the output.

So yeah guys, this is how you can merge every sheet in a workbook. Let me know if you have any query regarding this VBA code or any excel topic in the comments section below.
Download file:


Related Articles:

How to loop through sheets

how to get last row and column using vba

Popular Articles:

Many office men may need to merge multiple Excel Worksheets into a single master worksheet if they want to analyze or count the data quickly and easily. Sometimes they could merge the worksheets manually, but if there are numerous worksheets, merging sheets manually would be time-consuming. Now I would introduce merging multiple Excel Worksheet to One Worksheet through VBA.

How To Merge Excel Sheets

Now we suppose that you have three Worksheets contains some information about the students and now you would like to merge them into a Worksheet. Please follow the under steps if you are interested in learning merging multiple Excel Worksheet to one Worksheet through VBA.

Step 1: Press the ALT + F11 keys at same time to open the Microsoft Visual Basic for Applications window.

How To Merge Excel Sheets Together

Step 2: Click Insert>>Module, then paste the under codes into the newly opened module.

Sub Combine()
Dim Sun 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 Sun = 2 To Sheets.Count
Sheets(Sun).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

Attention: If your sheet contains numerous columns of data, just set the Range in the last part of the code as big as possible.

Step 3: Press F5 or click the run icon in the toolbar. Then you would see there is a new sheet called Combined appearing in the Excel sheet line.

Related Articles: