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

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.
finance_teacherAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
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

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.