Automatic list merging

[Bartekelese]

Hello, I have many tables of many tools. Everyone can add new table by userform (inserting new sheet) . Every table has the same amount of columns with the same column names. There is one table per one sheet. I'm preparing to have about 100 sheets in this file. Every sheet name starts with 'XMX' and the rest is the number. There is only 4 additional sheets for areas, categories etc.
Is there any possibility to automaticly merge all tables into one table?

 

[DanteAmor]

Hi and welcome to MrExcel

Try the following code. Just fit the name of the sheet and the table to do the merge.

Sub MergeAllTables()
  Dim sh As Worksheet
  Dim tb1 As ListObject, tb2 As ListObject
  Dim lr As Long
  
  'Sheet and table name to consolidate all tables
  Set tb2 = Sheets("Consolidate").ListObjects("MergeTable")
  
  For Each sh In Sheets
    If LCase(Left(sh.Name, 3)) = LCase("XMX") Then
      For Each tb1 In sh.ListObjects
        tb2.ListRows.Add AlwaysInsert:=True
        lr = tb2.DataBodyRange.Rows.Count
        tb1.DataBodyRange.Copy tb2.ListRows(lr).Range
      Next
    End If
  Next
End Sub