Combining data from multiple tabs in excel

[laina222]

I have an excel file that was given to me with dozens of tabs. Each tab has the same column headings with different data. I need to get all this data onto one tab. Is there a less tedious method than copying and pasting?

Thanks

 

[Zathras]

Here is a VBA routine that can do the job:

It assumes your data begin with column names in row 1 and have data in the other rows with no blank rows. If any of those assumptions are wrong, please post back with more details about exactly how your data are formatted.

Sub CombineData()
Dim sNewSheet As Worksheet
Dim sSheet As Worksheet
Dim rData As Range

  With Sheets(1)
    Intersect(.UsedRange, .Rows(1)).Copy
    Set sNewSheet = Sheets.Add
  End With
  With sNewSheet
    .Name = "Composite"
    .PasteSpecial
  End With
  Application.CutCopyMode = False
       
  For Each sSheet In Sheets
    If sSheet.Name <> sNewSheet.Name Then
      With sSheet.Range("A1").CurrentRegion.Offset(1, 0)
        .Copy sNewSheet.Range("A65536").End(xlUp).Offset(1, 0)
      End With
    End If
  Next
End Sub

Alt-F11 to open the VBA editor.
Insert / Module (from the VBA editor menu)
Copy and paste the code.
F5 to run.