Copy and paste using AdvancedFilter problems

[Hammer_757]

I need to copy values from cells based on a criteria and past them into a range on a separate sheet. i have names ranges setup for the source (1145 cells in a column) and the target range (single cell to paste down from). I also set a cell with the criteria because I am attempting to use AdvancedFilter. however, now that I been trying, I think that I cannot do this with the advanced filter for a few reasons.


My source range cells contain formulas and the formula is what gets pasted rather than the values.
The source cells contain the word "OK" or values like "T6-08", "T1-52" etc. i need to copy the TX-XX values and not the OKs. I tried to use <> but apparently I'm limited to plan old "="
For some reason only one cell in the target ranges gets populated even though there are about 1150 cells that would qualify as I have it now (criteria = "OK".

Before I give up on using advancedfilter can someone tell whether or not I can do this.
I would be grateful for an example of another method to a similar problem.

Please Login or Register  to view this content.

Thanks
Robert

[davesexcel]

Try auto filter

[Hammer_757]

Try auto filter

Thanks Dave. I hunted around the net and recorded a few macrow of Auto Filter and came up with the following:

Please Login or Register  to view this content.

Its a bit of a hack but it works.

one of the examples I worked off of included Application.Calculation = xlCalculationManual (and switching back to auto in the end).
Im not sure if its required, removing it doesn seem to effect anything but I left it in incase it prevents an unsen error that may come up with filters.

Robert