Excel table copy

Question

I have a excel table in file A. The number of rows and columns varies everytime. I have to copy the table from this excel table in file A to another file B. I am trying to write a VBA so that everytime file B is opened . The table named "XYZ" is refreshed. I can copy the content easily but copying the header is a problem. it does not work. I tried to keep the header static (as in below script) and copied only the data below headers but that does not solve my problem as headers could be added or reduced. I also tried to drop the current table and copy the new one but in that case I loose the MACros that I have set on other sheets based on sheet X of file B.

Sub Refresh()

Dim MyFile As String
Dim Filepath As String
Filepath = ActiveWorkbook.Path


MyFile = "File A"
Sheet1.Rows(3 & ":" & Sheet1.Rows.Count).ClearContents
Workbooks.Open (Filepath & MyFile)
Worksheets("X").Activate
ActiveSheet.ListObjects("XYZ").DataBodyRange.Copy
Application.DisplayAlerts = False
              ActiveWorkbook.Close


ActiveSheet.Paste Destination:=Worksheets("X").Range("A3")
End Sub

Answer 1

I have the problem that you have not dealt in your script. In my case the headers are changing. ActiveWorkbook.Range("XYZ[#All]").Copy can be used but when I copy it destination file it does not overwrite the existing table in destination file. In my requirement it must over-write. Otherwise my macros in other work sheets of destination file show error #REF . I also tried to delete all data first and then paste but even then I got error #REF