OFFSET function with multiple table

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,251
could you post screenshot after execute code? (without expand)
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,251
ok, now I'd like to see screenshot after expand
if result is as you said before that means your headers of the tables are different (number of columns in source tables should be the same as in the result Query table)
Power Query is case sensitive so header , HEADER or Header are three different names

did you see my example file? (post#17)
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
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
 

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
365
Yes my header name is base on location name. Every table have deference header name...
 

Attachments

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
365
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
Where to place this code, and how to execute?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
Where to place this code, and how to execute?
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)
 

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
365
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)
It's working TQSM, very appreciate...(y)(y)(y)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,853
Messages
5,507,710
Members
408,645
Latest member
AndreG06

This Week's Hot Topics

Top