Link to home
Start Free TrialLog in
Avatar of Ladydee
Ladydee

asked on

multiple table in one worksheet into one master table using userform

Hi, i'm new in using excel macro and vba. In my workbook, there are large amount of uncluttered data in many table in one worksheet. I need help how to combine/merge/join multiple table in one worksheet into one master table into another worksheet using userform? i'm sorry if my explanation is confusing because I'm not so good in English. thank you for any help.
Avatar of Shums Faruk
Shums Faruk
Flag of India image

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

Avatar of Ladydee
Ladydee

ASKER

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

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:
User generated image
into one master table like this:
User generated image
one of my option is using userform:
User generated image
or any other suggestion? thank you for help.
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial