So I have a data table I'm using AutoFilter to filter results on.

I'm trying to figure out a way to use VBA to pull the top 5 cells from that filtered list and paste them onto a separate summary sheet but am trying to avoid having a Macro that will just go back and forth copying and pasting (flashing lights and all).

I've tried to get this done via formula but it always ends up returning duplicate values as the table gets filtered.

=INDIRECT("A"&MIN(IF(SUBTOTAL(3,OFFSET(A1,ROW(A1:A19)-ROW(A1),,1)),ROW(A1:A19))))

Does anyone have a straight forward way to pull the top 5 visible cells from a filtered array and paste them onto another sheet?

EDIT: working with Excel 2016

CodePudding user response：

=INDEX(MyRange,SMALL(IF(SUBTOTAL(3,OFFSET(INDEX(MyRange,1),ROW(MyRange)-MIN(ROW(MyRange)),)),ROW(MyRange)-MIN(ROW(MyRange)) 1),ROWS(A$1:A1)))

and copied down.

This will require committing as an array formula (i.e. with CTRL SHIFT ENTER) for your version of Excel.

Note that MyRange should be set so as not to comprise the header row.