VBA Copy adjacent AND non-adjacent named table columns

[Bleed_Air]

Hi everyone.

I have the following code which copies the named contiguous columns from the table, and I have other code which pastes the copied data onto another sheet. It works like a charm:

Range("Table1[[#All],[Column_1]:[Column_4]]").Copy

I want to add a couple of non-adjacent columns to the copy function; let's call them Column_6 and Column_9, so that I end up with something like this (which obviously doesn't work):

Range("Table1[[#All],[Column_1]:[Column_4],[Column_6],[Column_9]]").Copy

Is someone able to tell me what the symbol and formatting should be, for the code that goes between [Column_4] and [Column_6]? I've tried using @, & and commas as the separator (as suggested from another forum) and they didn't work. I don't know a lick about VBA and I picked up this code from another location which I now can't find, so that brings me here. Pointing me to a good source example is also appreciated, because I have Google'd to the ends of the earth and can't find the one I originally used.

Many thanks

 

[John_w]

Use Union to combine the ranges:

    Union(Range("Table1[[#All],[Col 1]:[Col 3]]"), Range("Table1[[#All],[Col 5]]"), Range("Table1[[#All],[Col 9]]")).Copy

 

[Bleed_Air]

Use Union to combine the ranges:

    Union(Range("Table1[[#All],[Col 1]:[Col 3]]"), Range("Table1[[#All],[Col 5]]"), Range("Table1[[#All],[Col 9]]")).Copy

You, my friend, are a life saver! Thank you so much for providing the answer I was looking for.