Forum Discussion
iliulian
Microsoft
Jul 17, 2025Combine table name with each value in the first column
Hello, I need a little help in Excel. So I have some tables of the following structure scattered across multiple sheets. (Fake data)
Nokia Phone model Color 6610i Blue 3310 Y...
HansVogelaar
Jul 17, 2025MVP
I assumed that Nokia is the name of the table. For a more generic solution, you might run the following macro:
Sub CombineTables()
Dim wsh As Worksheet
Dim tbl As ListObject
Dim rng As Range
ReDim arr(1 To 3, 1 To 1) As String
Dim r As Long
Dim c As Long
Dim m As Long
Dim n As Long
Application.ScreenUpdating = False
n = 1
arr(1, n) = "Manufacturer"
arr(2, n) = "Model"
arr(3, n) = "Color"
For Each wsh In Worksheets
For Each tbl In wsh.ListObjects
m = tbl.ListRows.Count
ReDim Preserve arr(1 To 3, 1 To n + m)
For r = 1 To m
n = n + 1
For c = 1 To 3
arr(1, n) = tbl.Name
arr(2, n) = tbl.DataBodyRange(r, 1)
arr(3, n) = tbl.DataBodyRange(r, 2)
Next c
Next r
Next tbl
Next wsh
Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
Set rng = wsh.Range("A1").Resize(n, 3)
rng.Value = Application.Transpose(arr)
wsh.ListObjects.Add SourceType:=xlSrcRange, Source:=rng
Application.ScreenUpdating = True
End Sub