OFFSET function with multiple table

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,251
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
You're welcome. Thanks for the confirmation. :)
 

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
It's not include the header, how to include it?
 

Peter_SSs

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

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
365
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?
Yes, because the header is also a data...
 

Peter_SSs

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

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
365
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.
 

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
365
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).
 

Peter_SSs

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,856
Messages
5,507,735
Members
408,647
Latest member
Nicho la zido

This Week's Hot Topics

Top