Copy data to an empty table

[SalimJ]

Hi,

Could anyone help me with the following code. It works when Table2 already contains data, but I have runtime error 91 when Table2 is blank. Why?

Sub Integration()
    Dim tbl1 As Range
    Dim tbl2 As Range


    Application.ScreenUpdating = False


    Set tbl1 = Sheet1.ListObjects("Table1").DataBodyRange
    Set tbl2 = Sheet2.ListObjects("Table2").DataBodyRange


    If tbl2 Is Nothing Then
        tbl1.Copy tbl2.Offset(0)
    Else
        tbl1.Copy tbl2.Offset(tbl2.Rows.Count)
    End If
    
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
End Sub

 

[bobsan42]

because when there is no data DataBodyRange is nothing, so tbl2 remains nothing, so eventually you remain with no destination defined to paste the data.
So you have to set it directly to the table range and skip the header row:

[B]If tbl2 is nothing Then 
set tbl2 = Sheet2.ListObjects("Table2").Range.Resize(1).Offset(1)
.....
[/B]

or something similar.

 

[SalimJ]

Thank you very much bobsan42, it works perfect