How to merge worksheets / workbooks into one worksheet?

When we use Excel, we usually need to merge multiple worksheets or workbooks into a single master worksheet, so that we can analyze and count the data quickly and easily. 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. Today, I will talk about some quick tricks for you to solve this problem.   Download Now

Merge all worksheets of active workbook into one worksheet with VBA code

Merge worksheets or workbooks into one worksheet with Kutools for Excel


arrow blue right bubble Merge all worksheets of active workbook into one worksheet with VBA code


Supposing you have three worksheets (Grade1, Grade2, Grade3) which contain some information of the students, and now you need to merge them into one worksheet. See following screenshots:

doc-merge-worksheets-into-one1
doc-button2



doc-merge-worksheets-into-one4




doc-button1
doc-merge-worksheets-into-one2
doc-button1
doc-merge-worksheets-into-one3

The following VBA code can help you to get data from all worksheets of active workbook together into a new single worksheet. At the same time, all of the worksheets must have the same field structure, same column headings and same column order. Please do as follows:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

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

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. Then press F5 key to run the code, and all the data in the workbook has been merged into a new worksheet named Combined which will add before all worksheets.

Notes:

(1.) Your data must start from A1, if not, the code will not take effect.

(2.) Your data must have the same structure.

(3.) This code only can combine all worksheets of the active workbook, if you want to merge worksheets from multiple workbooks, this code will not work.


arrow blue right bubble Merge worksheets or workbooks into one worksheet with Kutools for Excel

Maybe the above VBA code has some limitations for you, Kutools for Excel’s Combine utility can help you merge worksheets from a workbook or multiple workbooks quickly and easily, whether they have the same structures or not.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Read More     Free Download Now

Supposing you have two workbooks need to be merged, and the worksheets have not same structures, as following screenshots shown, in this case, Kutools for Excel’s Combine tool also can solve this problem.

doc-merge-worksheets-into-one7   doc-merge-worksheets-into-one8

After installing Kutools for Excel, please do as following steps:

1. Click Enterprise > Combine, see screenshot:

doc merge worksheets into one sheet 5

2. In the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into one worksheet option. See screenshot:

doc merge worksheets into one sheet 6

3. And click Next button, on step2 of wizard, all the opened workbooks and worksheets are listed in the list boxes, if you want to add other workbooks or folders, you can click Add button to insert the files you want to combine. Then specify the worksheets that you want to combine and also you can click button1 button to select the range that you want to use. (By default, the used range is selected for each worksheet) See screenshot:

doc merge worksheets into one sheet 7

4. After specify the worksheets and ranges, then continue to click Next button, on step3 of wizard, do the following settings as your need, and see screenshot:

doc merge worksheets into one sheet 8

5. After finishing the settings, click Finish button to combine the worksheets. And you will be asked if you want to save this scenario, see screenshot:

doc-merge-worksheets-into-one11

If you want to save this scenario, please click Yes, and enter the name of the scenario, (see following screenshot), and when you want to use this scenario, you just only need to click Scenario button to choose the name of the scenario you need on step2 wizard. If you don’t want to save this scenario, please click No.

doc merge worksheets into one sheet 9

And then all the selected worksheets in the workbooks have been merged into a new worksheet of a workbook. The worksheet information has been added before the data. See screenshot:

doc merge worksheets into one sheet 10

If you want to know more about this feature, please click Combine multiple worksheets from workbooks into one worksheet
Free Download Kutools for Excel Now.


arrow blue right bubbleRelative Articles:

How to combine multiple workbooks to one workbook in Excel?

How to combine worksheets of same name into one worksheet?

How to summarize data from worksheets / workbooks into one worksheet?

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink +21 Jason Mills
used your first Macro function. WOW! thanks for posting
2014-02-06 20:17 Reply Reply with quote Quote
Permalink +4 Edwin M
I think the code 'Selection.Offset(0, 0).Resize(Selec tion.Rows.Count - 1).Select' has not to be there. On my end, what it does is select the second row onwards missing the first.
2014-06-24 04:50 Reply Reply with quote Quote
Permalink 0 Razina K
I have the same problem. It's omitting the first row from the second worksheet added.
2015-12-10 18:00 Reply Reply with quote Quote
Permalink 0 KJ
Eliminates headers so you don't have duplicated headers
2017-02-10 15:49 Reply Reply with quote Quote
Permalink +3 Shannon
Works like a charm, thanks for sharing!
2014-03-06 21:20 Reply Reply with quote Quote
Permalink +1 Daniel
Thank you! The macro worked perfectly.
2014-03-11 18:35 Reply Reply with quote Quote
Permalink -5 M. Anandhi
i dont know, how to use the VBN function in excel, can you please help how to do that.
2014-03-12 09:34 Reply Reply with quote Quote
Permalink +1 David Tkemaladze
Awesome, Thanks!!! Very Very Usefull
2014-03-16 07:57 Reply Reply with quote Quote
Permalink 0 Yogesh
Macro one works fine. Thanks!! Its Awesome!!!! :)
2014-03-22 10:50 Reply Reply with quote Quote
Permalink +2 randy
Note this is limited by the 65k row limit, if you need to up the line Selection.Copy Destination:=Sh eets(1).Range(" A65536").End(xl Up)(2) range to resolve the problem.
2014-04-01 13:22 Reply Reply with quote Quote
Permalink 0 Craig
Great tool however, it does not combine all 21 sheets. How can I expand the range?
2016-08-16 21:15 Reply Reply with quote Quote
Permalink +5 taffyJ
NOTE!!! You need to be careful with the macro.. it will work if your data has a continuous flow of information and no blank row. this what the line "Selection.Curr entRegion.Selec t" does. For example, if you need to copy ALL the data on a spreadsheet but there is a blank line at (let's say) row 200, the macro will only copy down to row 200; IT WILL DISREGARD ALL DATA BELOW THAT ROW!!!
2014-04-22 14:43 Reply Reply with quote Quote
Permalink 0 Carlos
Does anyone knows a command to avoid this issue?? The problem with Selection.Offse t(1, 0).Resize(Selec tion.Rows.Count - 1).Select can be solved?

Selection.Rows.Count - 1) is the command originating the issue.

Anyone??
2016-08-30 13:39 Reply Reply with quote Quote
Permalink 0 Ed
Instead of starting from the top and going down, start from the bottom and go up
2017-04-05 04:00 Reply Reply with quote Quote
Permalink 0 arno
Does anyone knows the code to solve problems of empty rows and columns?. I have it in my data but there is no simple solution as starting bottom up?

Thanks!
2017-04-10 08:30 Reply Reply with quote Quote
Permalink 0 Diane
For deleting rows that are empty:
Option 1: Select cells in column A that need to be deleted. Use Ctrl - and then select Entire Row in popu up. Save workbook and exit. Then open workbook.
Option 2: Select rows that need to be deleted. Use Ctrl -. Save workbook and exit The open workbook. Gave option 1 so it was visible to you what was happening.

For deleting columns that are empty:
Select column. Ctrl -. Save workbook and exit The open workbook.
2017-04-13 01:07 Reply Reply with quote Quote
Permalink 0 Eyan
Thank you ! My 2nd row was empty in all sheets & I was confused.
2017-01-27 23:25 Reply Reply with quote Quote
Permalink 0 Liz
the vba code worked great but I need the combined worksheet to update as I add new rows or new data to the cells in the worksheets that I am combining. Is there a way to do that? Thank you for your help.
2014-05-06 20:21 Reply Reply with quote Quote
Permalink 0 Liz
The macro code worked great for the first combined worksheet, but I need the combined worksheet to update as I add data to the existing worksheets that I am combining. Is there a way to do that?
2014-05-06 20:23 Reply Reply with quote Quote
Permalink 0 MALLORY ANDERSON
Did you ever figure out how to get it to update in the master as you add in rows to the other worksheets?
2016-10-19 17:18 Reply Reply with quote Quote
Permalink +2 Anto
Excellent work.. This has been reduced my work from 2 hours to 10 minutes.
2014-05-26 11:23 Reply Reply with quote Quote
Permalink +1 Idris Runijawala
Excellent, I used both first macro and Kutools, both has reduced my data gathering and compilation work tremendously. Thanks for sharing.
2014-05-31 15:41 Reply Reply with quote Quote
Permalink 0 Alper
I just erased the part given below and now it only updates. Hope this helps...

Sheets(1).Select
Worksheets.Add
2014-06-13 12:38 Reply Reply with quote Quote
Permalink -1 Angela Fish
Code works great! I need the macro to copy all data on the sheets. It is cutting off the first row after Sheet 1. Only sheet one has the header row, the other sheets are just data. Is this possible?
2014-06-16 19:22 Reply Reply with quote Quote
Permalink 0 Adnan
I have question about "Merge all worksheets into one worksheet with VBA code" :The code work very well, but only when it is empty, it adds the header, it is possible to modify the code to say not to consolidate the empty sheet ?
2014-06-30 13:02 Reply Reply with quote Quote
Permalink +1 Driehlax
Thanks you saved me an hour of copy and paste :D
2014-07-09 01:05 Reply Reply with quote Quote
Permalink +1 Debbie
Have two workbooks, same data but second one has extra data and some data has been updated,
inv# Status
123 7/2 open

inv# status
123 7/2 open 7/7 closed

how to merge into one removing or highlighting duplicates and adding any new inv# not in the first one
2014-07-09 18:05 Reply Reply with quote Quote
Permalink 0 Jogan
Works great if your Column A is non-=blank. On sheets with column A blank, it seems to be overwriting. In my dtaa column E is a non blank on all sheets. How can I modify the Copy Destination to account for blank column A on some sheets?
2014-07-18 18:34 Reply Reply with quote Quote
Permalink +2 clopez
Sub CopySheet()
'
' CopySheet Macro
'

'

Dim i As Integer

For i = 2 To 25
Sheets("Sheet1" ).Select
Range("A1").Sel ect

Selection.End(x lDown).Select


Range("A" & CStr(Selection. Row) + 1).Select


Sheets("Sheet" & CStr(i)).Select
Range("A2").Sel ect
Range(Selection , Selection.End(x lDown)).Select
Range(Selection , Selection.End(x lToRight)).Sele ct
Selection.Cut
Sheets("Sheet1" ).Select
ActiveSheet.Pas te

Next


End Sub
2014-08-06 17:11 Reply Reply with quote Quote
Permalink 0 RamN
Range("A:A").SpecialCells(xlCellTypeLastCell).Select
ActiveCell.Offset(1, 0).Select
'use this code to select last cell in column "A"


Quoting clopez:


Sub CopySheet()
'
' CopySheet Macro
'

'

Dim i As Integer

For i = 2 To 25
Sheets("Sheet1").Select
Range("A1").Select

Selection.End(xlDown).Select


Range("A" & CStr(Selection.Row) + 1).Select


Sheets("Sheet" & CStr(i)).Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
Sheets("Sheet1").Select
ActiveSheet.Paste

Next


End Sub
2014-09-25 18:02 Reply Reply with quote Quote
Permalink +1 Jene
MACRO CODE WORKED SO FINE... FAB JOB :-)
2014-09-02 09:28 Reply Reply with quote Quote
Permalink 0 mlybeer
This macro seems to be pretty on-target, however some of my data is being clipped off somehow and I cannot figure out why! Ahhaa, It only adds the next tab if it has more rows than the previous tab! How can I fix this???
2014-09-03 07:46 Reply Reply with quote Quote
Permalink +1 Sharon
Hi,

I have used the following code:

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(Selec tion.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

But for some reason it is missong out one of my worksheets? Does anyone know why?

Thanks
2014-09-23 08:55 Reply Reply with quote Quote
Permalink 0 Rose
Hi Sharon,

I've had this happen before. Depending on how many tabs you are trying to combine, you may actually hit the row limit of 2010... I didn't get a warning, it just didn't copy.
2014-10-06 20:09 Reply Reply with quote Quote
Permalink 0 Sergen
you are awesome. thank you so much
2014-10-21 14:41 Reply Reply with quote Quote
Permalink 0 Dee
Very useful. Have been trying to figure this out for days. Thanks!!
2014-11-02 12:39 Reply Reply with quote Quote
Permalink 0 AM
I notice that for this to work all column headings have to be the same. What if you have the case that the row headings are the same and you want to combine by adding columns to your unified worksheet?
2014-11-17 17:15 Reply Reply with quote Quote
Permalink 0 Gazza
How do you get the code to ignore a particular worksheet?
2014-12-01 11:37 Reply Reply with quote Quote
Permalink 0 Sidddharth
Thanks. Works like charm
2014-12-01 18:48 Reply Reply with quote Quote
Permalink 0 Vijay Tiwari
thanks alot, it is very helpful for me and time saving arround 90 minutes.
2014-12-02 14:57 Reply Reply with quote Quote
Permalink 0 JC
Wow whwat a time saver! Thank you!
2014-12-03 16:30 Reply Reply with quote Quote
Permalink 0 famousk
very useful tip. thanks.
2015-02-07 22:01 Reply Reply with quote Quote
Permalink 0 Chris
Amazing, thanks! This is the best tip ever
2015-02-24 16:59 Reply Reply with quote Quote
Permalink 0 LouV
Wow!!!! This is incredible, thanks. Saved me a great deal time. I'm talking various Workbooks with 900 tabs each. Worked great, thanks.
2015-03-03 23:44 Reply Reply with quote Quote
Permalink 0 Rhwny
Splendid solution!!! I just googled this problem and you provided the perfect solution.
2015-04-10 12:20 Reply Reply with quote Quote
Permalink 0 Chief
Thanks for the code. very helpful. I tried this code in a worksheet where some data is protected and it didn't populate. Any help on how to get this to work on a protected sheet?
2015-04-13 19:15 Reply Reply with quote Quote
Permalink 0 Chief
Amazing solution. how can you modify the code to work on cells that are protected?
2015-04-14 12:40 Reply Reply with quote Quote
Permalink 0 kristin
please help me, and you did. I am so thankful, in life for the work experience, and even though I did the prints, I got a good , or even better a even review. I love my new job.
2015-04-15 20:12 Reply Reply with quote Quote
Permalink 0 Kedar
I have one simple question (I'm not a VB programmer).

Please tell me how to modify that rule to paste the results inside already created worksheet (let's say "COMBINED") and not create new one?
2015-04-23 11:39 Reply Reply with quote Quote
Permalink 0 Kedar
At this moment I have got smth like this:

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

Everything works just great. The only thing I want to do is to add some automatic refresh for VBA rule. I want to add new content to other Worksheets and see results at last one with VBA.
2015-04-23 15:21 Reply Reply with quote Quote
Permalink 0 Antonia
Did you ever figure out how to do this?
2016-07-08 22:57 Reply Reply with quote Quote
Permalink 0 cherry codilla
please help me i cant find the merge worksheet. where i can find it?

thank you,
cherry
2015-04-27 03:53 Reply Reply with quote Quote
Permalink 0 Chris
Life saver. Thank you.
2015-04-28 15:22 Reply Reply with quote Quote
Permalink 0 Mary
This is exactly what I need. One issue: I don't want the headings from each tab, just the data. How do i tell it to not copy all the headings?

Thank you.
2015-05-20 17:16 Reply Reply with quote Quote
Permalink 0 Mary
This is perfect for what I need. Thank you.

How can I tell it to not copy the headings on each tab, just the data. I'm ending up with headings in my combine tab 40 times...

Sorry if this posts twice. I'm not seeing it in the refreshed comments...
2015-05-20 17:23 Reply Reply with quote Quote
Permalink 0 maryam
hello
thanks alot for your merge program.you help me that my boss be happy :lol:
2015-05-25 05:17 Reply Reply with quote Quote
Permalink 0 akmuseums
it's only compiling about a 1/4 of my data! how can I get the rest of my sheets into it?

thank you!
2015-07-14 06:45 Reply Reply with quote Quote
Permalink 0 kamal
same problem here...i have 20 sheets but it only combines 3 sheets
2015-12-08 08:34 Reply Reply with quote Quote
Permalink 0 Bhumika
I just used the Merge Worksheet function. Worked like a charm! Thank you SO much for posting this! It is a life saver. Even the MS office website didn't list this...
2015-07-17 15:33 Reply Reply with quote Quote
Permalink 0 Melisa
I have a workbook with 7 sheets that I would like to combine.

Each of the sheets is a check list and the completion date is in a different column on each sheet depending on the number of steps so on one sheet it is column H another it is column AR for example.

I thought that the best way to get around this issue would be to make a named Data set which includes the desired columns as the columns would be in the same order regardless of which sheet it is on.

Is there any way to modify this code to looks at the dataset on the sheets rather than all active cells? and keeps information in the same columns?
2015-07-29 20:22 Reply Reply with quote Quote
Permalink 0 Derek
Thank you for this! With a little tweaking of datasets, this worked like a charm.
2015-07-31 22:17 Reply Reply with quote Quote
Permalink 0 Searcher
The code works great, but if the macro is not resident in the Active worksheet, if works on the wrong worksheet. I adapted it it to work with the Active workbook as follows:

Sub Combine()
Dim J As Integer

On Error Resume Next

With ActiveWorkbook

.Sheets(1).Sele ct

.Worksheets.Add ' crteate a new worlsheet; it becomes the new SHEETS(1)
.Sheets(1).Name = "Combined" ' Call it 'COmbined'. Hopefully there is no conflict!
.Sheets(2).Acti vate ' go to the first user's worksheet
Range("A1").Ent ireRow.Select ' Select Row 1
Selection.Copy Destination:=.S heets(1).Range( "A1") '
For J = 2 To .Sheets.Count ' these are the User's original worksheets
.Sheets(J).Acti vate
Range("A1").Sel ect
Selection.Curre ntRegion.Select
Selection.Offse t(1, 0).Resize(Selec tion.Rows.Count - 1).Select ' adjust selection by removing Row 1
Selection.Copy Destination:=.S heets(1).Range( "A65536").End(x lUp)(2) ' Assumes Column A always has data
Next ' worksheet

End With

End Sub
2015-08-02 22:23 Reply Reply with quote Quote
Permalink 0 sung
Great, I didn't need this code. but the code listed seems not perfect, so started read and this seems great!!..

I know i might need the code soon or later so i'm taking it

Quoting Searcher:
The code works great, but if the macro is not resident in the Active worksheet, if works on the wrong worksheet. I adapted it it to work with the Active workbook as follows:

Sub Combine()
Dim J As Integer

On Error Resume Next

With ActiveWorkbook

.Sheets(1).Select

.Worksheets.Add ' crteate a new worlsheet; it becomes the new SHEETS(1)
.Sheets(1).Name = "Combined" ' Call it 'COmbined'. Hopefully there is no conflict!
.Sheets(2).Activate ' go to the first user's worksheet
Range("A1").EntireRow.Select ' Select Row 1
Selection.Copy Destination:=.Sheets(1).Range("A1") '
For J = 2 To .Sheets.Count ' these are the User's original worksheets
.Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select ' adjust selection by removing Row 1
Selection.Copy Destination:=.Sheets(1).Range("A65536").End(xlUp)(2) ' Assumes Column A always has data
Next ' worksheet

End With

End Sub
2016-07-07 13:07 Reply Reply with quote Quote
Permalink 0 Subhashini K
I downloaded the tool but it is not working. I want to combine multiple sheets. I open the requisite excel sheets and then as soon as I click on enterprise and then combine, all the sheets disappear. Nothing happens and if I try to open them again I get the alert that the sheets are locked by another user. I have to restart my system to again work on them. Can you please suggest a solution for this?
2015-08-21 05:52 Reply Reply with quote Quote
Permalink 0 Omar
Thanks a million :-)
2015-08-23 13:07 Reply Reply with quote Quote
Permalink 0 Leslie
Great, worked perfectly!
2015-09-03 14:16 Reply Reply with quote Quote
Permalink 0 Rodrigo
you saved my lyfe...tks
2015-09-03 21:57 Reply Reply with quote Quote
Permalink 0 Peter
Is there a solution to merge 6 tables into one row, please?

It`s hard to describe, but I have converted a pdf into excel and it simply pasted the date into separate sheets, means every 6 sheet would be one row and I have around 6136 pages...

Thanks,
Peter
2015-09-15 20:44 Reply Reply with quote Quote
Permalink 0 Elahi baksh
Thank u very much this program reduces my efforts of doing copy paste job
2015-09-16 05:36 Reply Reply with quote Quote
Permalink 0 Mikael Hjelm
Thanks for the macro. Now I can use it with empty rows.
I add an extra kolumn A (with all tabs marked) and fill it down with any letter as deep the largest table have rows.
Well I did not check just estimated and used advsnced search with only unique values selected, and over the whole cell area, to remove the empty rows that comes in between in the Combine tab.
2015-10-15 15:08 Reply Reply with quote Quote
Permalink +1 yathi
hi thanks for the code,

it works great. .

can you plz help me in getting the respective worksheet names in front of the data which are combined.

i.e if i am combining 2 columns of data from 20 worksheets, i want to get one more column of data which gives name of the sheet that particular data is fetched from.

thanks in advance

Yathish
2015-11-01 09:23 Reply Reply with quote Quote
Permalink +1 Vijay Dilli
I have this need as well to have an additional column with the worksheet name as 1st column. Is this possible? I am not very familiar with VBA and I am not sure I can make these changes myself with out impacting the existing results.

Vijay
2016-06-23 05:22 Reply Reply with quote Quote
Permalink 0 kamal
when i run the code it only merges 3 sheets only out of total 20 sheets.any body please help
2015-12-08 08:32 Reply Reply with quote Quote
Permalink 0 Clive
Can't get the macro to work in excel 2013. But I think its because I am trying to combine 18 sheets in 1 workbook.
2016-01-18 15:14 Reply Reply with quote Quote
Permalink 0 Maurice
In lieu of a donate button I'm gonna get kutools for my personal computer. This little macro saved me and my 20 coworkers from having to enter more than 9,200 accounting entries for our payroll. Thanks, guys!
2016-01-22 14:17 Reply Reply with quote Quote
Permalink 0 Ravi
Thank's Brother I have visited many times on this page but i couldn't recognised now finally I find it. Thanks ;-)
2016-01-24 20:13 Reply Reply with quote Quote
Permalink 0 Dhanapal
Wow very nice tool to merge multiple sheets....Super b
2016-01-27 11:14 Reply Reply with quote Quote
Permalink 0 Somasekhara G
Thank you so much the initial code is worked for me. But i need to know that, i have some blank rows in the middle, but i need to copy the entire data. would like to know how to modify this. Thanks again.
2016-03-02 07:42 Reply Reply with quote Quote
Permalink 0 dan
Does anyone know of a way to have the combined worksheet to ouput the actual data values only from the other sheets, as opposed to formulas? The formulas don't translate to the combined page.
2016-03-24 16:35 Reply Reply with quote Quote
Permalink 0 Trey
I have 12 worksheets (1 for each month of 2015), running Excel 2010. Each worksheet has approximately 45k rows. When I run the macro it copies about 6 of the the 12. There should be plenty of rows. If I start from scratch and run the VBA again, it still only combines the same month/worksheet s? I checked my headers and I think they all match, but maybe that could be the issue?
2016-04-18 13:44 Reply Reply with quote Quote
Permalink 0 Andre
Thanks a lot for the combine code
2016-05-04 08:40 Reply Reply with quote Quote
Permalink 0 Gabriel
Thank you!
The code worked perfectly!
2016-05-24 17:56 Reply Reply with quote Quote
Permalink 0 ronald bthakur
this is awesome to merge the multiple spreadsheets using macro, but can anyone help if i can do the same process using excel functions....be cause i do not know the vba coding....
2016-06-13 17:56 Reply Reply with quote Quote
Permalink 0 ronald bthakur
Hello Everyone,
This is awesome to merge the multiple worksheet into one worksheet via vba coding or macros, but can anyone help me to merge data from multiple worksheet in to one blank worksheet using excel functions.
Thanks
2016-06-15 20:09 Reply Reply with quote Quote
Permalink +1 Nat
When using the VBA providied above, is there a way for the combined sheet to automatically update when the sheets that is combined are added to it, so it is always running?

Thanks!
2016-06-20 16:43 Reply Reply with quote Quote
Permalink 0 Janett
Thank you, this has helped me a lot!
2016-07-01 09:57 Reply Reply with quote Quote
Permalink 0 Guzman
Hi ! Thanks for the code.
About -"Merge all worksheets of active workbook into one worksheet with VBA code"-

Is it possible to do it with a For Each construct to navigate through the Sheets?
Thanks.
2016-07-08 19:31 Reply Reply with quote Quote
Permalink 0 Antonia
The VBA code worked great! If I update one of the worksheets (i.e. enter more data) will that data automatically be entered into my new 'Combined' worksheet?
2016-07-08 22:53 Reply Reply with quote Quote
Permalink 0 steve
you rock, thanks a bunch....
2016-08-04 17:05 Reply Reply with quote Quote
Permalink 0 Saleem Ansari
hi.

i want to merge the data only two sheet. but it should not create in new sheet.

it should be merge from sheet 1 to sheet 2

please help.
2016-08-10 12:34 Reply Reply with quote Quote
Permalink 0 Daniel
How do you omit a sheet?

I have 3 sheets(Tracker) (Archive)(Note Options). I want to combine only (Tracker)(Archi ve)sheets to a master sheet.

I am also trying to find a code that will auto refresh Combined sheet when new information is listed on (Tracker)(Archi ve)sheets .

Please help if possible. Code Sample listed below.

Sub Combine()
Dim J As Integer
Dim wrk As Workbook
On Error Resume Next

Set wrk = ActiveWorkbook

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.Scr eenUpdating = False


Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Master"

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(Selec tion.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)


Application.Scr eenUpdating = True
Next

End Sub

Sub Worksheet_Chang e(ByVal Target As Range)

If Intersect(Targe t, Me.Range("a:l") ) Is Nothing Then Exit Sub
Application.EnableEvents = False

Call TrapTest

Application.EnableEvents = True

End Sub
2016-08-10 17:44 Reply Reply with quote Quote
Permalink 0 Ray and Eric
Thank you for the code! We had to copy 200 sheets into one, just one comments regarding the range; the maximum row/range is
1,048,576 rows by 16,384 columns
2016-08-12 15:23 Reply Reply with quote Quote
Permalink 0 Saleem Ansari
Hi Daniel,

thank for your support & information but my problem is still not resolve.

i have four sheet in one workbook with the name of (Reason Code), (Offline Code), (Work File 1) and (Work File 2).

i want to merge the data of only between two sheet (Work File 1) and (Work File 2) but it should not create any new sheet, data should be merge in (work file 2) sheet from work file 1
2016-08-16 13:17 Reply Reply with quote Quote
Permalink 0 Kiran Kanth
Hi,

thanks a lot this saved my time in consolidating 80 to 100 sheets in one work book.
thanks once again


Kiran Kanth
2016-09-01 05:52 Reply Reply with quote Quote
Permalink 0 Shireen
My scenario....... but I need the function to refresh data as both sheets are added to and info is updated daily (so the ranges would also increase).

2 worksheets in two different workbooks
Data is formatted exactly the same in both sheets

I want to consolidate these sheets into one sheet (and keep the formulas), but as the data changes daily I would need to be able to refresh the data from the source documents.

Can you assist?
2016-10-11 13:13 Reply Reply with quote Quote
Permalink 0 Shireen
My scenario - I require to be able to refresh the data from the source data also:

2 worksheets in two different workbooks - the same formatting
I need to bring the data across from both worksheets into one worksheet - in a separate workbook
As the data is added to and changed daily I would need to refresh this data - this also means that the range in these worksheets would also change if rows are added.
These sheets are also read only when being worked on by the User, so I would need to refresh each time the data is saved.
2016-10-11 13:20 Reply Reply with quote Quote
Permalink 0 Eduardo
Used your first Macro and worked! Great work, thanks a lot!

Kind regards
2016-10-21 15:03 Reply Reply with quote Quote
Permalink 0 Maher
Thank, I used Kutools, it is so easy to use. Thanks again!
2016-10-26 13:31 Reply Reply with quote Quote
Permalink -1 Mike
This isn't a merge...it's simply stacking data. Change the title, please.
2016-11-03 17:27 Reply Reply with quote Quote
Permalink 0 VJ
How can I modify the code to paste as value only?
2016-11-22 17:09 Reply Reply with quote Quote
Permalink 0 Jennifer
This worked great, saved me a lot of time, but it only merged up to column J and I need it to go up to column T. Can someone please help??? This is for my job!!
Please help me not look dumb in front of my boss, thanks
2016-12-15 21:28 Reply Reply with quote Quote
Permalink 0 Lloyd
I am using the VBA below with great effect with all the sheets in workbook but now I am trying to exclude one sheet name " OAL Index" and just cant seemed to get it right. It keeps selecting all the sheets

Any points to the right direction would be appreciated.

Sub Combine()
Dim J As Integer

On Error Resume Next
Sheets(1).Selec t
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' copy headings
Sheets(2).Activ ate
Range("A2").Ent ireRow.Select
Selection.Copy Destination:=Sh eets(1).Range(" A2")

' work through sheets

For J = 2 To Sheets.Count ' from sheet 2 to last sheet
If Sheets(J).Name "OAL Index" Then Sheets(J).Selec t Replace:=False
Sheets(J).Activ ate ' make the sheet active
Sheets(J).Selec t
Range("A1").Sel ect
Selection.Curre ntRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offse t(2, 0).Resize(Selec tion.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sh eets(1).Range(" A65536").End(xl Up)(2)
Next
End Sub



Thanks All
2016-12-23 01:20 Reply Reply with quote Quote
Permalink 0 Martin
When I try to use this VBA, it caps out at 79,160 lines (including header). Any suggestions on how to tweak the code or could it be the data in the spreadsheets.
2016-12-28 00:36 Reply Reply with quote Quote
Permalink 0 Chandra Mouli
Excellent Job ....buddy u have refine my 1 hour ....
THANKS
2016-12-30 06:48 Reply Reply with quote Quote
Permalink 0 Kabir Humayun
1st thanks for the code,
I modified the code following way,
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(0, 0).Resize(Selec tion.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(3, 0).Resize(Selec tion.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(2)
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(115, 5).Resize(Selec tion.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("E65536").End(xlUp)(2)
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(116, 5).Resize(Selec tion.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("F65536").End(xlUp)(2)

Next
End Sub

When i tried with this code only around 5/10 sheet, it works fine, but when i tried this code for around 400 sheets then got 2 wrong value and after 255 row no data come for only one column.

Another columns data are okey and came.

any idea, basically i need to call data (from some specific cell) from around 400 sheets any idea ?
2016-12-30 13:45 Reply Reply with quote Quote
Permalink 0 Kabir Humayun
How can i collect specific cells data from around 400 sheet... where all sheet are same format.

for ex. I want to get data from A3, A4, A29, B30, C34 and store on a new sheet.

thanks in advanced.
2016-12-30 13:48 Reply Reply with quote Quote
Permalink 0 GeneA
For those people who want to update data they already merged you can use this code:

Sub Combine()
Dim J As Integer
On Error Resume Next
'Delete the sheet "Combined" if it exist
Application.Dis playAlerts = False
On Error Resume Next
ActiveWorkbook. Worksheets("Com bined").Delete
On Error GoTo 0
Application.Dis playAlerts = True

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(Selec tion.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub


----------------------------------

Hope that helps!!!!
2017-01-11 04:21 Reply Reply with quote Quote
Permalink 0 Kayla
Your macro worked BUT it added each sheet multiple times. i have no idea why...I had 4 sheets with invoice lists (1 septembers, 2 octobers Ect.)
and for some reason it added my september back on 2 more times and added eachother one underneath 3x each....
I did not modify or delete anything from the macro just simply copied pasted and ran it...
2017-01-18 16:35 Reply Reply with quote Quote
Permalink 0 angry_banana
Hi! Can you help me with this problem.
I have an excel doc with 12 sheets. In each sheet is a table. With a differend cells format (width & height).
I have to copy all sheets in one and save each cells format. Thanks!
2017-02-22 14:30 Reply Reply with quote Quote
Permalink 0 CRG
Quoting Nat:
When using the VBA providied above, is there a way for the combined sheet to automatically update when the sheets that is combined are added to it, so it is always running?

Thanks!

Would like to know the answer to this. Thanks for the great Macro!
2017-02-22 18:29 Reply Reply with quote Quote
Permalink 0 boopathi raj
Very much Thanks ... ... :lol:
2017-04-03 05:39 Reply Reply with quote Quote
Permalink 0 Elvedin
I receive spreadsheets where table header is in starts in row 3. So I need to go to each worksheet to delete columns 1 and 2 for the macro to work. Can I adjust the code, so it would start in A3 instead of A1
2017-04-10 14:28 Reply Reply with quote Quote
Permalink 0 Diane
Attempted VBA Code. Have 5 worksheets in 2 workbook that need to be combined. All same structure. VBA code will only combine 2 of the worksheets rather than all 5. What do I change in the VBA code to make combine all 5 worksheets? Data in worksheets is all under the 65K .xls row limit and I am using Excel 2016.
2017-04-13 01:12 Reply Reply with quote Quote

Add comment


Security code
Refresh