In our daily work, we may encounter a problem that merge hundreds of sheets or workbooks into one sheet for analyzing data which takes a large of time if you use the Copy and Paste command in Excel. Here in this tutorial, I will provide some tips for quickly solving this job.
QUICK NAVIGATION >Merging all sheets of active workbook into one sheet with VBA >Merging dozens of sheets or workbooks into one worksheet/workbook with clicks >Merging two tables into one and update by a column with clicks >Download sample file

In this section, I provide a VBA code which will create a new sheet to collect all sheets of the active workbook while you running it.

1. Activate the workbook you want to combine its all sheets, then press Alt + F11 keys to open Microsoft Visual Basic for Applications window.

2. In popping window, click Insert > Module to create a new Module script.

3. Copy below code and paste them to the script.

Sub Combine() 'UpdatebyExtendoffice 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

4. Press F5 key, then all data across sheets have been merged in to a new sheet named Combined which is placed in the front of all sheets.

Do You Want To Have A Pay Raise and Much Time To Accompany With Family? Office Tab Enhances Your Efficiency By 50% In Microsoft Office Working Right Now Unbelievable, working at two or more documents is easier and faster than working at one. Compareed with well-known browsers, the tabbed tool in Office Tab is more powerful and more efficient. Reduce hundreds of mouse-clicks and keyboard typing every day for you, say goodbye to the mouse hand now. If you usually work at multiple documents, Office Tab will be a great time-saver for you. 30-day free trial, no credit card required. Read MoreFree Download Now

With the VBA, you only can combine sheets in the active workbook, but how can you merge sheets across workbooks to a sheet or workbook?

Merge sheets across workbooks into one sheet

Merge sheets across workbooks into one workbook

For solving this job and satisfy other requirements on sheets-combination, the Combine function has been developed with four combination scenarios:

• Combine multiple sheets or workbooks into one sheet
• Combine multiple sheets or workbooks into one workbook
• Combine same name sheets into one sheet
• Consolidate values across sheets or workbooks into one sheet

Here takes the second option as instance:

Combine multiple sheets or workbooks into one workbook

After free installing Kutools for Excel, please do as below:

1. Activate Excel, click Kutools Plus > Combine, a dialog pops out to remind you the workbooks you want to combine needed be closed. Click OK to continue.

2. In the Combine Worksheets step 1 dialog, check Combine multiple worksheets from workbooks into one workbook option. Click Next to go to next step of wizard.

3. Click Add > File or Folder to add the workbooks you want to combine to the Workbook list pane, then you can specify which worksheet will be joined together by checking names in Worksheet list pane. Click Next to go to the last step of wizard.

4. In this step, specify the settings as you need. Then click Finish.

5. A window pops out for you selecting a folder to place the combined workbook, then click Save.

Now the workbooks have been merged into one workbook. And at the front of all sheets, a master sheet named Kutools for Excel is also created which lists some information about the sheets and links for each sheet.

Free Trial Say Goodbye To Mouse Hand and Cervical Spondylosis Now

300 advanced tools of Kutools for Excel solve 80% Excel tasks in seconds, pull you out of the thousands of mouse-clicks.

Easily deal with 1500 working scenarios, no need to waste time for searching solutions, have much time to enjoy your life. Improve 80% productivity for 110000+ highly effective people every day, of course including you. No longer to be tormented by painful formulas and VBA, give your brain a rest and joyful working mood. 30-day free trial with full features, 30-day money back without reasons. A Better Body Creates A Better Life.

Read More Download Now

If you want to merge two tables into one and update data based on a column as below screenshot shown, you can try the Tables Merge utility of Kutools for Excel.

Kutools for Excel: more than 200 handy Excel add-ins to simplify complicated tasks into a few clicks in Excel

After free installing Kutools for Excel, please do as below:

1. Click Kutools Plus > Tables Merge to enable Tables Merge wizard.

Kutools for Excel

30-Day Free Trial 300 Advanced Tools Solve 80% Excel Problems Improve 80% Working Efficiency Be An Excel Expert, Gain People's Recognition

2. In the step 1 of the wizard, you need to separately select the main table and lookup table ranges. Then click Next.

3. Check the key column you want to update data in the main table based on. Click Next.

4. Then check the columns in main table you want to update the data based on lookup table. Click Next.

5. In the step 4 of the wizard, check the columns you want to add from lookup table to the main table. Click Next.

6. In the last step of the wizard, specify the setting options as you need. Then click Finish.

Now the main table has been updated the data and add new data based on the lookup table.

Recommended Productivity Tools

Office Tab - Tabbed Browsing, Editing, Managing Of Documents In Microsoft Office 2019 - 2003 And Office 365

Full- featured free trial in 30 days

A Professional Add-in for Accelerating Excel 2019-2007, shrink hours tasks to seconds

This add-in includes dozens of professional groups, with 300+ options will automate most of your daily tasks in Excel, and increase your productivity at least by 50%. Such as groups of one-click optons and batch conversions.
Now it's your chance to speed up yourself with Kutools for Excel!

Full- featured free trial in 60 days