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

Posted on 2011-04-26
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

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This article will show you how to use shortcut menus in the Access run-time environment.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now