Excel vba - copy an advanced filter range then transpose function

Hi,
I would like to select a specific range/column in the output range of an advanced filter function, and then copy/paste special the results from the query to the next empty row in another worksheet using transpose function. I gather that the array size to copy will change each time a new query is run on the data, and that the range to copy to/paste special/transpose would change, and that these need to be the same size in order for the function to work. Any suggestions for VBA code for the advanced filter/select data/edit/copy/paste special/transpose function?

Perhaps something like this?

Please Login or Register  to view this content.

Where the B column is the filtered column youu wish to copy from.

Alf

Hi, thanks for this, I tried this code, all I got was the cells highlighted with a dashed line, and the usual message 'select destination & press ENTER, or choose PASTE'.
Many thanks,
Jonathan

The macro does what you asked for i.e.

advanced filter/select data/edit/copy/paste special/transpose function?

Just add the

Please Login or Register  to view this content.

after the "PasteSpecial" line to get rid of marching "ants" and "select dest" message,

and if you do wish to get rid of the autofilter as well use

Please Login or Register  to view this content.

Alf