VBA to use cell reference as filter

[Ronak Parekh]

Hello,

I have uploaded a sample file in which at sheet 1, when I select values from list in cell B1 to B6, it should extract those rows matching those filters from 'sheet 2'. Sheet 2 contains all row data while I wish to have the filtered data copy pasted in sheet 1 using VBA.

I am not great in writing the VBA code so I tired something by recording it but it was not at all working good. It would be great if someone could help me with this.

Also I had used 'Data Validation' to create the drop-down list in cell B1 to B6 but as the values in row data are duplicate, the drop-down list too shows the duplicate selection. I can correct it by creating a separate list for data validation but again if there is some easier way for this then I would be happy to know about it.

Thanks in advance for your help!

 

[eibi]

Ronak,

A pivot table will automatically create the filters you want and show the applicable records.

I'd start here: http://chandoo.org/wp/excel-pivot-tables/

Pivot tables are powerful tools, and Chandoo has some excellent guidance on learning to use them.

 

[Luke M]

See attached, which used AdvFilter to copy the data. As for the duplicates, I'd suggest either:

1. Remove drop-down, let user free-enter data
2. Use AutoFilter on Data, forget Dashboard
3. Use a PivotTable

 

[Deepak]

Hi,

Have a look at http://www.exceltip.com/tips/introduction-to-use-advance-filter.html

 

[Ronak Parekh]

thanks for your quick comments guys;

using advance filter is bit cumbersome as I would always have to insert the input after opening the advance filter dialog box. A more bitter problem is when we the first filter applied is having higher number rows then the next filter applied then the bottom rows would stay unwanted and I would have to manually delete those.

Pivot table is also a great option but I would be more comfortable in having the data in same format as I have in sheet 2. Pivot table some what changes the format of table and provides limited option with the 'Values' option.

though thanks for your reply guys

 

[Luke M]

Ronak,

If you had looked at my file, you'll see that the AdvFilter is automatic, no need to "input after opening the advance filter dialog box".

 

[Ronak Parekh]

Ohh yes.. thanks Luke..

I was confused after reading about Advance filter from the web link you have provided..

Thanks Again