OFFSET function with multiple table

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
  1. 365
I have 100++ sheet (table) with same format with difference high (total row not fix, will change). How to combine all table (sheet) in one sheet only. I prefer OFFSET function, any other solution is welcome. TQSM
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Screen shot
 

Attachments

  • Untitled-3.fw.png
    Untitled-3.fw.png
    72.5 KB · Views: 9
Upvote 0
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:
Upvote 0
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
 
Upvote 0
Yes my header name is base on location name. Every table have deference header name...
 

Attachments

  • Untitled-4.fw.png
    Untitled-4.fw.png
    109.1 KB · Views: 6
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,495
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top