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
 
Sorry about wrong information to give to you, but I'm very appreciate effort to solve my problem...
no problem but you didn't read my posts carefully even if text is bolded so most of them are wasted
have a nice day
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You're welcome. Thanks for the confirmation. :)
 
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
It's not include the header, how to include it?
 
Upvote 0
It's not include the header, how to include it?
It should include the header just once at the top of the new sheet.

Do you mean that you want the headers re-copied with every table?
 
Upvote 0
Yes, because the header is also a data...
Sounds a bit unusual but let's see if this does what you want then?

VBA Code:
Sub CombineTables_v2()
  Dim i As Long
  
  Sheets.Add Before:=Sheets(1)
  For i = 2 To Sheets.Count
    With Sheets(i)
      If .ListObjects.Count > 0 Then .ListObjects(1).Range.Copy Destination:=Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With
  Next i
  Sheets(1).Rows(1).Delete
End Sub
 
Upvote 0
Sounds a bit unusual but let's see if this does what you want then?

VBA Code:
Sub CombineTables_v2()
  Dim i As Long
 
  Sheets.Add Before:=Sheets(1)
  For i = 2 To Sheets.Count
    With Sheets(i)
      If .ListObjects.Count > 0 Then .ListObjects(1).Range.Copy Destination:=Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With
  Next i
  Sheets(1).Rows(1).Delete
End Sub
It's took all my resources, LOL, with my old lappy, it's cannot execute, may be I'll try with dekstop.
 
Upvote 0
It should not take any more resources than the post 24 code to which you said
With desktop i5 9th generation, my desktop hang and restart. It's working with several table only (I create another file), not for all 145 table (original file).
 
Upvote 0
Does it make any difference we copy/paste values only?

VBA Code:
Sub CombineTables_v3()
  Dim i As Long
  
  Sheets.Add Before:=Sheets(1)
  For i = 2 To Sheets.Count
    With Sheets(i)
      If .ListObjects.Count > 0 Then
        .ListObjects(1).Range.Copy
        Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
      End If
    End With
  Next i
  Sheets(1).Rows(1).Delete
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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