How To Combine All Worksheets Into One Worksheet In Excel || Combine Multiple Sheets into One Sheet

How To Combine All Worksheets Into One Workbsheet In Excel || Combine Multiple Sheets into One Sheet

In this tutorial, let us learn how to combine multiple sheets into a single workbook using a simple VBA code.

This code assumes that all the worksheets have the same column headings and in the same order. This code will copy all the rows into one worksheet called Master.

Steps:

1. Copy the code as mentioned

Sub CopyFromWorksheets()
Dim wrk As Workbook 'Workbook object - Always good to work with object variables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim rng As Range 'Range object
Dim colCount As Integer 'Column count in tables in the worksheets

Set wrk = ActiveWorkbook 'Working in active workbook

For Each sht In wrk.Worksheets
If sht.Name = "Master" Then
MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _
"Please remove or rename this worksheet since 'Master' would be" & _
"the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error"
Exit Sub
End If
Next sht

'We don't want screen updating
Application.ScreenUpdating = False

'Add new worksheet as the last worksheet
Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
'Rename the new worksheet
trg.Name = "Master"
'Get column headers from the first worksheet
'Column count first
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(1, 255).End(xlToLeft).Column
'Now retrieve headers, no copy&paste needed
With trg.Cells(1, 1).Resize(1, colCount)
.Value = sht.Cells(1, 1).Resize(1, colCount).Value
'Set font as bold
.Font.Bold = True
End With

'We can start loop
For Each sht In wrk.Worksheets
'If worksheet in loop is the last one, stop execution (it is Master worksheet)
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets
Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount))
'Put data into the Master worksheet
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
Next sht
'Fit the columns in Master worksheet
trg.Columns.AutoFit

'Screen updating should be activated
Application.ScreenUpdating = True
End Sub

2. Open the workbook that contains worksheets you want to combine.

3. Press Alt+F11 or right click on any sheet tab and select view code, to open the Visual Basic Editor (VBE).

4. From the menu, choose Insert-Module and then paste the code into the code window at the right side.

5. Save the file and close the Visual Basic Editor.

6. Now, go back to the Excel workbook and go to view tab, under macors, select view macros and runt the CopyFromWorksheets macro.

7. You will observe that a new sheet called Master has been created which has collated all the data from the three sheets.

So friends, in this way one can combine or collate data from multiple worksheets/ tabs into a single worksheet.

If you found this video valuable, give it a like. If you know someone who needs to see it, share it. Leave a comment below with your thoughts. Add it to a playlist if you want to watch it later.

***********************************************

Tools that I use for Vlogging:

***********************************************
You Can Connect with Me at

Follow:

[greghoets]

I thank you very much It worked perfectly. Greg from South Afirca. Thank you. People like you are rare.

[velmurugandhandabani]

Very nice tutorial. I have benefited . super

[pegwinn]

Excel lent. My question was answered quickly and now I will fix my issue. Well Done.

[francisokyere]

Thanks for the video.
Please, I have one page of the data in a table form in the sheet and want to make 100 copies of the same data in the same excel sheet. That's page 1 to page 100 in the same excel sheet. How can I do it? Any help.

[nikhil.m.a]

Excellent, It was very helpful. Thank you Boss

[carliadams]

This worked great and thank you, but what about the data that gets added to the individual sheets? I cannot get the new data to transfer over.

[umar]

Thanks a lot dear..

Another request is to
Make a searchable table in ms access.

Like i have three tables, , ist i made three queries...

Now i make an another table to search the results that is present in ist three tables...

[Ahmed-nulu]

Hi sir,
My sheets has more rows, from sheet to sheet, do we consolidate all the sheets?

[aakashmb]

Hey can you give me the code to combine all sheets in a workbook into a single sheet in a new workbook.
It is same as your solution in above video but I need the output to be in a new sheet in a new workbook. Please help me in this.

[krisbrynda]

Hi, worked but the formatting is not copying over. Is there a a code I can add to copy all formatting over?.. I have several different columns and rows with different format.

[timfrikkee]

Thank you very much for the video. However, for some reason it skips a few sheets in my file for some reason. I checked and all headers are the same as well as columen numbers. What constraints does the macro contain for why it might skip a sheet?

[govindsingh]

Sir make video on sap ps (sap project system), and what are differences between sap ps and primavera

[joseantoniomaresarrieta]

does not work with big datasets, it seems anything above 70-80k records is unhandled and cant spot that limit in code, does that is Excel default behavior?

[mbs]

its working for only first 3 columns any body suggest me how to rectifie this