Copy specific table columns from one workbook to another

[ddualba]

Greetings,

I am looking to copy a few columns from a table in another workbook without specifically referring to the sheet name if possible. This is the code I have that works

Sub Export_SomeData ()


Dim myBook As Workbook, newBook As Workbook


Set myBook = ThisWorkbook
Set newBook = Workbooks.Add




With newBook


    myBook.Sheets("Sheet1").Range("MyDataTable[[#Data], [tColumn_3]]").Copy
        Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False




    myBook.Sheets("Sheet1").Range("MyDataTable[[#Data], [tColumn_5]]").Copy
        Range("B2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


End With


End sub

I tried using the following code I found in a search to set a range and then copy it but I received the following error on that line when I do.
Error 1004: Application-defined or object-defined error

set rng = myBook.Names("MyDataTable[[#Data], [tColumn_3]]").RefersToRange

Any ideas on how I could refer/copy a table column in another workbook without referring to the sheet would be appreciated as I want to prevent the code from breaking if the table gets moved or sheet gets renamed.

Thanks

 

[pgc01]

Hi

Try:

Sub Export_SomeData()


Dim myBook As Workbook, newBook As Workbook
Dim lstObjFrom As ListObject


Set myBook = ThisWorkbook
Set lstObjFrom = Application.Range("MyDataTable").ListObject
Set newBook = Workbooks.Add

lstObjFrom.ListColumns("tColumn_3").DataBodyRange.Copy
    Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


lstObjFrom.ListColumns("tColumn_5").DataBodyRange.Copy
    Range("B2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


End Sub

 

[ddualba]

Yes,

This works flawlessly.

Thank you so much.

 

[pgc01]

You're welcome.