Get cells value based on a criteria

Hi, I need a VBA code to look in sheet "All" and find all cells in column A that are equal to the type specified in sheet1, and then copy their corresponding value and profit in columns B and C. I provided a sample of a small data, but my list would be like 100000 entries, so the code should not stop at a fix row number (in this case A9), but instead at the last filled cell in column A. Thanks in advance.

Not sure this is what you want but in Sheet1:

Please Login or Register  to view this content.

Hi chiidzzz,

Find the attached that has these features:
1. A Dynamic Named Range on the All Tab that will grow to the size of your data set.
2. A Advanced Filter of the AllData to work using a Macro (I simply recorded a macro)
3. On changing the Sheet1 cell of A2 it will fire the advanced filter macro.

I think this is what you want. Change the yellow cell value and see what happens. Look at the VBA behind the sheet and in the module to see it work.

On Dropdown Change Adv Filter.xlsm

I've edited the program and I'll post it if you're interested.

Thanks xladept, what you've done works great, and this is what I need. however, my real columns location are different than the sample provided.
(they are now in sheet 3 of the sample)
The "type" is in column L, "value" is in column P, and "profit" is in column Q.
And all the other columns contain data.
What should I change in your code?

You're welcome and thanks for the rep!

The new format isn't as convenient - but:

Please Login or Register  to view this content.

You're welcome!