VBA Copy cells after filter

[MrNoSoul]

Hi, 

I've read several other discussions but still can't find anything that seems to match what im trying to do or that i understand enough to make use of. 

I have a table on sheet 1 which has 16 columns (A to P) with the headers starting from A3. I want to filter the table based on if Column P has yes in it. Only one row will have "yes" at a time. I then want to copy the cell from Column A, N and O onto Sheet 2 and paste them individually into B3, C3 and D3. 

Thank you in advance.

[#2]

I would write a formula in cells Q4 R4 S4 that are =A4, =N4, =O4 (then copy these down, or even better if possible put your data is in a ctrl T Table so the formula automatically fills down) you can then highlight these 3 new formula after filtering P and copy and paste them as values into Sheet3 cell B3

[#3]

@MrNoSoul As a variant, and if you want to dynamically present the three values in Sheet2, based on where the "Yes" is in Sheet1. If the row in Sheet1 with "Yes" changes, the cells in Sheet2 will update automatically.

Two (of many different) options: 

1) a "double" FILTER, spilling the three cells in one go (provided your Excel version supports it)

2) a straight forward set of INDEX and MATCH functions

See attached workbook.