Filtering to a specific row and then selecting a specific visible cell using VBA

[lakshman]

Hi all,

I have data about the equity products we release everyday. First, I want to filter for producers in my team under "Product Requestors" in Column P:

ActiveSheet.Range("$A$1:$AM$77").AutoFilter Field:=36, Criteria1:=Array( _
        "John", "Jane", "Jack"), Operator:=xlFilterValuesv

Then, I want to filter for a particular stock ticker from Column N:

ActiveSheet.Range("$A$1:$AM$77").AutoFilter Field:=14, Criteria1:="CAPL SP"

This will typically leave only one row. Within the one row, I want to select the cell that has the product's Minimum Notional Value. This falls under Column H.

Each time I do a different filter, the selected cell would naturally be different. I want this so that I can eventually use my Macro (I've already created this for another task, thanks to the wonderful MrExcel community) to copy paste this value into another sheet.

I would really appreciate it if anyone can help me out on this!

 

[lakshman]

Hey. That doesn't work. It says Paste Range Class failed. I looked up online and I read that you cannot mix PasteSpecial with Copy Destination but I'm not sure of how to not mix them and get the result I want.

 

[frank_AL]

laksham I got it to work by separating into 2 lines of code.

copyRange.SpecialCells(xlCellTypeVisible).Copy 
pdttype.Range("C11").PasteSpecial(xlPasteValues)

</pre>