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?

Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet4").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll, Transpose:=True

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

Application.CutCopyMode = False

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

ActiveSheet.AutoFilterMode = False

Alf