Copy data from a table into an empty table

[taleb]

Hi,
I have a problem that I need to delete the table records before I paste data into it. The problem is once I delete all the records, Excel VBA (BUG?) will not recognize it as a ListObject. Does anyone know a workaround?

This code:
Sheet111.ListObjects("Target_Table_name").DataBodyRange.PasteSpecial xlPasteValues

will give this error:
Run-time error '91':

Object variable or with block variable not set

It works fine if the table contains records.

Any suggestions?

 

[John_w]

That error occurs when DataBodyRange is Nothing, meaning there are no data rows in the table. Try this:

    With Sheet111.ListObjects("Target_Table_name")
        If Not .DataBodyRange Is Nothing Then .DataBodyRange.PasteSpecial xlPasteValues
    End With

Post your complete code if you need more help.

 

[JoeMo]

Have you tried this?

Sheet111.ListObjects("Target_Table_name").Range.Offset(1,0).PasteSpecial xlPasteValues

 

[taleb]

Have you tried this?

Sheet111.ListObjects("Target_Table_name").Range.Offset(1,0).PasteSpecial xlPasteValues

OMG! That worked. Thank you very much Joe.

Since you obviously are a hero genius, can you have a look at my other post: https://www.mrexcel.com/forum/excel...e-query-unlink-refresh-problem-bug-maybe.html

 

[JoeMo]

OMG! That worked. Thank you very much Joe.

You are welcome - thanks for the reply.