Combine Multiple Sheets

[rjbinney]

Hey, Mr. Excel!

I have multiple tabs in one workbook, need to get them all in one. All columns are titled the same. Data on each tab is Formatted As Table.

My Googling keeps returning two options - "Consolidate" function, but I just want everything to pop into one tab. I don't want to SUM, VAR, or nothin'. Other option is Get Data/Power Query, which no matter what I do, turns everything into a muskrat's ********.

Help would be groovy. And helpful!

Love,

 

[Peter_SSs]

You could give this macro a try with a copy of your workbook.
It assumes there is a Table (ListObject) on each worksheet.

Sub Combine_Tables()
  Dim wsS As Worksheet
  Dim i As Long, nr As Long
  
  On Error Resume Next
  Set wsS = Sheets("Summary")
  On Error GoTo 0
  If wsS Is Nothing Then
    Sheets.Add.Name = "Summary"
    Set wsS = Sheets("Summary")
  End If
  With wsS
    .Move After:=Sheets(Sheets.Count)
    .UsedRange.Clear
    Sheets(1).ListObjects(1).Range.Rows(1).Copy
    .Range("A1").PasteSpecial xlPasteValues
    For i = 1 To Sheets.Count - 1
      nr = .Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
      Sheets(i).ListObjects(1).DataBodyRange.Copy
      .Range("A" & nr).PasteSpecial xlPasteValues
    Next i
    .ListObjects.Add xlSrcRange, .UsedRange, , xlYes
  End With
End Sub