AutoFilter selected Value in Another Sheet

[appu_gusai]

hi all

suppose there is an excel sheet having a table consisting 27 column and 1500 row, now if i apply AutoFilter Command in that table and find some values in 15 or 16 rows....

now i want to displayed AutoFilter values of 15 or 16 rows to another sheet of same nos. of row and column. so how it is possible, please let me know?

thanking you.

 

[Gary's Student]

This is easy with a "helper" column. In column 29 (AC) in cell AC2 enter:

=IF(SUBTOTAL(3,B2:B2)=0,MIN($AC$1:AC1)-1,SUBTOTAL(3,$B$2:$B2))

and copy down. However the filter is applied, each visible row will show a simple sequential list of integers (1,2,3,...).

You can then refer to the visible rows with INDEX()/MATCH()

 

[appu_gusai]

please find the attached excel file for sample work

thanking you....

 

[NARAYANK991]

Hi ,

What is your objective ?

By the following statement :

i want to displayed AutoFilter values of 15 or 16 rows to another sheet of same nos. of row and column.

what do you mean ?

Do you want the displayed rows to be copied to Sheet2 using a macro ? Or do you want to use formulae ?

If you use formulae , then if do a second AutoFilter changing the criteria , the earlier set of results will be overwritten with the new set.

If you use a macro , then do you want the new set to be appended to the earlier set ?

Surely the easiest thing to do is just select the visible range and do CTRL C , CTRL V ?

Narayan