Solved: multiple table in one worksheet into one master table using userform

Shums Faruk

3/29/2017

Hi,

I am not sure about the number of tables or DataBody of every table. I also hope you are referring to real table object not just Excel range.
Assuming your Data Sheet is Sheet1.
Please try below code, which will copy every table from Sheet1 to Master Sheet with the header leaving one row space in between to identify table contents.

Sub CopyTables()
Dim MstrSh As Worksheet, DataSh As Worksheet
Dim iTable_Count As Integer
Dim oListObject As ListObject

With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

'Delete Previous Master Worksheet & Insert a New Blank Worksheet With Same Name
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("MasterSheet").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "MasterSheet"
Application.DisplayAlerts = True
Set MstrSh = Worksheets("MasterSheet")
Set DataSh = Worksheets("Sheet1")

    iTable_Count = DataSh.ListObjects.Count
     
    If iTable_Count <= 0 Then
        MsgBox "No Tables found here!!!"
        Exit Sub
    End If
     
    For Each oListObject In DataSh.ListObjects
         'copy tables with headers...
        oListObject.Range.Copy Destination:=MstrSh.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Next

With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

Ladydee

3/31/2017

ASKER

use same question and want to added some attachment for this question.

Ladydee

3/31/2017

ASKER

Hi, I'm sorry. here i attach sample for this question. Is there any code/script can be use to merge/join/combine multiple tables in same worksheet:

into one master table like this:

one of my option is using userform:

or any other suggestion? thank you for help.