Advanced Filtering Copy certain columns

[Philips]

I'm using Advanced Filtering and the code is copying all the filtered rows to a new tab. Now I don't want to copy everything but only selected columns by range.

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#000000]Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Data"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Tabel1[#All]"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]AdvancedFilter Action[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000]xlFilterCopy[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] CriteriaRange[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000] _
Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Data"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"AG1:AL2"[/COLOR][COLOR=#000000]),[/COLOR][COLOR=#000000]CopyToRange[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000]Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]“Filter”[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]“B10″), Unique:=True
 [/COLOR]</code>

I've tried something like this but there is a syntax error:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#000000]Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Data"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Tabel1[#All]"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]AdvancedFilter[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] CriteriaRange[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000] _
Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Data"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"AG1:AL2"[/COLOR][COLOR=#000000]),[/COLOR][COLOR=#000000]Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Data"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A1"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Copy _
destination[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000]Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Filter"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"B10"[/COLOR][COLOR=#000000]),[/COLOR][COLOR=#000000]Unique[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#800000]True
[/COLOR]</code>

Any ideas how to select certain columns?

 

[Marcelo Branco]

Welcome to Mr Excel

Not sure if i understand what you need, but let me try. Suppose you have a table with four columns and you want the Advanced Filter makes a copy of only two columns, for example, Name and Value.

Put the columns' headers you want to copy (Name and Value) in Sheets Filter B10:C10 and in Advanced Filter, use
Copy to: Filter!B10:C10

Hope this helps

M.

 

[Philips]

Welcome to Mr Excel

Not sure if i understand what you need, but let me try. Suppose you have a table with four columns and you want the Advanced Filter makes a copy of only two columns, for example, Name and Value.

Put the columns' headers you want to copy (Name and Value) in Sheets Filter B10:C10 and in Advanced Filter, use
Copy to: Filter!B10:C10

Hope this helps

M.

Thanks for your answer, I fixed it already...