Sub CombineTables()
Dim i As Long
Sheets.Add Before:=Sheets(1)
For i = 2 To Sheets.Count
With Sheets(i)
If .ListObjects.Count > 0 Then
If Sheets(1).UsedRange.Address = "$A$1" Then
.ListObjects(1).Range.Copy Destination:=Sheets(1).Range("A1")
Else
.ListObjects(1).DataBodyRange.Copy Destination:=Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End If
End With
Next i
End Sub
Where to place this code, and how to execute?If they are formal Excel tables (ListObjects) then try this with a copy of your workbook. It should create a new worksheet before all the other worksheets and build the combined table on that new worksheet.
I'm assuming at most one table on each worksheet.
VBA Code:Sub CombineTables() Dim i As Long Sheets.Add Before:=Sheets(1) For i = 2 To Sheets.Count With Sheets(i) If .ListObjects.Count > 0 Then If Sheets(1).UsedRange.Address = "$A$1" Then .ListObjects(1).Range.Copy Destination:=Sheets(1).Range("A1") Else .ListObjects(1).DataBodyRange.Copy Destination:=Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1) End If End If End With Next i End Sub
so re-read post#13Yes my header name is base on location name. Every table have deference header name...
1. With your workbook active press Alt+F11 to bring up the vba window.Where to place this code, and how to execute?
Sorry about wrong information to give to you, but I'm very appreciate effort to solve my problem...
It's working TQSM, very appreciate...1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code I posted into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro 'CombineTables' & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)