Excel 2000/2007/etc --> merge tabs ?

Posted on 2011-04-26
Medium Priority
Last Modified: 2012-06-27
I have one Excel file that has 200+ TABS

Does anyone have a method to merge all these tabs into one ?

Basically I don't want to copy/paste (CTRL, END, A) for hours to get this all into one tab.
Question by:finance_teacher
1 Comment
LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 35468152
The routine below merges all worksheets in a workbook into the active worksheet. See the comments in the code for how to customize the routine.

Public Sub MergeWorksheets()

' Merges all worksheets in a workbook into the active worksheet.

   Dim SourceWorksheet As Worksheet
   Dim SourceRange As Range
   Dim DestRow As Long
   ' Change the following constant if the first source row is not row 2 on each worksheet
   Const FirstSourceRow = 2
   Application.ScreenUpdating = False
   DestRow = IIf(ActiveSheet.UsedRange.Address = "$A$1", 1, ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row + 1)
   For Each SourceWorksheet In ThisWorkbook.Worksheets
      If Not SourceWorksheet Is ActiveSheet Then
         If SourceWorksheet.UsedRange.Rows.Count >= FirstSourceRow Then
            ' Replace this line with a more specific reference if necessary
            Set SourceRange = SourceWorksheet.UsedRange.Offset(FirstSourceRow - 1).Resize(SourceWorksheet.UsedRange.Rows.Count - FirstSourceRow + 1)
            ActiveSheet.Rows(DestRow).Resize(SourceRange.Rows.Count, SourceRange.Columns.Count).Value = SourceRange.Value
            DestRow = DestRow + SourceRange.Rows.Count
         End If
      End If
   Next SourceWorksheet
   Application.ScreenUpdating = True

End Sub


Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question