Stacking multiple tables - Runtime 91 object variable

[ShelleyBelly]

Hi All,

I've the below code which does the trick but I get a runtime error 91 on the last line starting with "tbl" . I've used on error resume and it will happily work, but this is ofcourse not the answer. I believe the error lies in the fact that the data body range will be empty following the first if statement, but the first line starting with "tbl" doesn't throw an error. Any help would be much appreciated

Private Sub CombineTracks()
Application.ScreenUpdating = False
    
Dim Tbl As ListObject
Dim Mastertbl As ListObject
Dim ws As Worksheet




Set ws = Limbs
Set Mastertbl = Body.ListObjects("Body")


'Delete all table rows except first row
    With Mastertbl.DataBodyRange
        If .Rows.count > 1 Then
            .Offset(1, 0).Resize(.Rows.count - 1, .Columns.count).Rows.Delete
        End If
    End With


'Loop through each table in the worksheet
    For Each Tbl In ws.ListObjects
        If Mastertbl.DataBodyRange.Rows.count = 1 Then
            Tbl.DataBodyRange.Copy destination:=Mastertbl.DataBodyRange(1, 1)
        Else
            Tbl.DataBodyRange.Copy destination:=Mastertbl.DataBodyRange(Mastertbl.ListRows.count + 1, 1)
        End If
    Next Tbl


Application.ScreenUpdating = True
End Sub

 

[wideboydixon]

Untested but this might work:

            Tbl.DataBodyRange.Copy Destination:=Mastertbl.DataBodyRange(1, 1).Offset(Mastertbl.ListRows.Count, 0)

WBD

 

[ShelleyBelly]

Hi WBD,

Thanks for the swift reply. Fraid it didn't work though and I still get runtime 91!

Thanks for trying

Tom

 

[wideboydixon]

Go to that line in the code and press F9 to set a breakpoint. Now run the code and it should stop on that line. Press Ctrl+G to bring up the immediate window and try each of the following:

? Tbl.DataBodyRange.Address
? Mastertbl.DataBodyRange.Address
? Mastertbl.ListRows.Count

Presumably one of them will generate error 91 - which one?

WBD

 

[ShelleyBelly]

So i tapped my way through and is hung up on the last copy when it gets to a table that is empty. I've added and if line to skip empty tables

'Loop through each table in the worksheet
    For Each Tbl In ws.ListObjects
        If Not Tbl.DataBodyRange Is Nothing Then
            If Mastertbl.DataBodyRange.Rows.count = 1 Then
                Tbl.DataBodyRange.Copy destination:=Mastertbl.DataBodyRange(1, 1)
             Else
                Tbl.DataBodyRange.Copy destination:=Mastertbl.DataBodyRange(Mastertbl.ListRows.count + 1, 1)
            End If
        End If
    Next Tbl

thanks for the help in hunting the problem

Tom

 

[wideboydixon]

Good debugging. You're welcome.

WBD