How to select multiple, non-adjacent, columns in Excel filtered table using column names

Question

I'm trying to copy multiple, non-adjacent, columns from filtered table to another sheet. I'm stuck with the multiple selection part:
* The data is from external source -> I can't control original stracture
* The filtering creterias are from columns which I don't need to copy -> I can't skip importing columns that I don't need to copy.
* The table is filtered -> I can't simply select column (e.g. C:C), I need the filtered rows only.

Is it possible doing so by using header names?

I've tried few lines of code (see below) but none of those produced the desired results. If I record a macro I simply get range selections:
Range("C155:C30230,E155:E30230").Select

This is what i tried so far, grouped by results:
Note that these is test code so it is not necessarily refer to the same columns -> Working, but not exactly what I want:
' this is only one column Range("myTable[[#Headers],[Email]]").Select

' this is full (unfiltered) column selection Union(ws.Range("C:C"), ws.Range("E:E")).Select


' this is not using header name Union(ActiveSheet.ListObjects("myTable").ListColumns(3).Range, ActiveSheet.ListObjects("myTable").ListColumns(5).Range).Select

->These snippets generatad run-time error "Method 'Range' of object
'_Worksheet' failed":
Union(Range("myTable[[#Headers],[Email]]").Select, Range("myTable[[#Headers],[Language]]").Select)

Dim rng As Range Set rng = Union(Range("myTable[[#Headers],[Email]]"), Range("myTable[[#Headers],[Language]]"))

Union(Range("myTable[[#Headers],[Email]]"), Range("myTable[[#Headers],[Language]]")).Select

Union(Range("myTable[[#Headers],[Email]]"), Range("A:A")).Select

->This snippet generatad error "Compile error: Type mismatch":
Dim rng As Range Set rng = Union(Range("myTable[[#Headers],[Email]]").Address, Range("myTable[[#Headers],[Language]]").Address)

My aim at this stage is to see all the required filtrered table columns selected.
Thank you for looking at this

Answer 1

Using structured references as you show, I think you want something like:

Set R = Union(Range("Table1[[#All],[Email]]"), Range("Table1[[#All],[Language]]")).SpecialCells(xlCellTypeVisible)

R.Copy 'your_destination

Some of your errors are related to:

• you are not providing range objects for the arguments to the Union method
• you are only providing the Header row to the argument
• Use of Select just confuses things and is rarely, if ever, necessary.

Answer 2

This works for me.

Sub x()

With ActiveSheet.ListObjects(1)
    Union(.ListColumns(1).Range, .ListColumns(3).Range).SpecialCells(xlCellTypeVisible).Copy Range("A18")
End With

End Sub

There is a good guide to tables here.