Autofilter looping through criteria list

[philwaters]

Good morning

I have a simple spreadsheet which shows countries and currencies. I want to autofilter based on currency code. The list of currency codes are on worksheet named 'Criteria'

I want to copy and paste the filtered results. This part is ok.

Can someone show me how to avoid writing code that manually includes each currency and instead loop through the list on the Criteria worksheet and stops when it reaches the end?

So if I want to filter by USD, copy and paste the filtered results and then move to CNY, then CAD etc until I reach the end of the list - how is this written?

[karmapala]

I want to autofilter based on currency code. The list of currency codes are on worksheet named 'Criteria'

Maybe something like this ?

Please Login or Register  to view this content.

The code will filter sheet Currencies with criteria from all Currency Code value in sheet Criteria.

[philwaters]

Maybe something like this ?

Please Login or Register  to view this content.

The code will filter sheet Currencies with criteria from all Currency Code value in sheet Criteria.

Hi karmapala

Thanks for the response. I haven't had a chance to implement your suggestion above yet but I will.

On first glance though, would this code filter by ARS then copy and paste the filtered results to a different sheet and then move on to AUD and repeat the process until the end of the list?

I appreciate the copying and pasting is not covered above, I have this part sorted elsewhere. I need to input the instruction that will do the filtering by the first criteria on the list and then once done move to the next.

Will your code do this?

[karmapala]

Hi philwaters,
No, I'm sorry ... the code don't do the copy paste because I read your post as follow:

I want to copy and paste the filtered results. This part is ok.

would this code filter by ARS then copy and paste the filtered results to a different sheet
and then move on to AUD and repeat the process until the end of the list?

Sorry, the code is not filtering one by one (via loop)...
It's not :
Read ARS in sheet Criteria,
then filter sheet Currencies column B with ARS
(then do the copy/paste the filtered result)

Read the next list in sheet Criteria which is USD,
then filter sheet Currencies column B with USD
(then do the copy/paste the filtered result)

But :
Read all the criteria value in the list of Sheet Criteria,
then filter sheet Currencies column B with all those values.
So, it's just like when we do manual filter,
we check each item in it according what we want.

Example :
In sheet Criteria, the list are :
ARS
AUD
CAD
CNY

If we do it manually,
first we untick all the ticked item in the filter dialog box column B sheet Currencies,
then we tick ARS, tick AUD, tick CAD, tick CNY ....
Then the filtered result is only the currency which are ARS, AUD, CAD, CNY.

The code do like that.

So if later you want to do copy/paste,
I think (maybe?) you can just directly copy the filtered result, because they are all already there.

[philwaters]

Thanks for the reply.

I understand what your code will do now.

However, I require code that will filter through each of the criteria list one by one, performing copy and paste tasks and then moving on the next list item. Right now I have this implemented by code that specifically names each criteria item (on error used if no entry found on main worksheet). I was wondering if code could loop through the list so I didn't require the lengthy code for individual criteria items.

[karmapala]

However, I require code that will filter through each of the criteria list one by one

Do you mean that you want to stop/pause the code ?

For example :
The code read ARS in the list on Sheet Criteria.
Then the code filter sheet Currencies column B with ARS... then it pause,
so you can see the filtered result of ARS...
Then you continue the code which is

performing copy and paste tasks and then moving on the next list item.

So, the code go to the next cell which has USD value in the list on Sheet Criteria,
Then the code filter sheet Currencies column B with USD... then it pause,
so you can see the filtered result of USD....

and so on.

Please correct me if I'm wrong.

If it's not like that, I can't think another situation,
because even if the code line is to filter each currency in the list sheet Criteris one by one, then copy it also per each currency,
but I think once the code is run, we don't have a time to see the filtered result for each currency.
Also, if the computer is quite fast, I don't think we have the time to see the paste result increasing per each currency.
So, to be honest I still can't see what is the difference result between filtering one by one to each currency than filtering all at once.

Right now I have this implemented by code that specifically names each criteria item
(on error used if no entry found on main worksheet).

Oh i see now. I think then it is a different case,
because the criteria list is not based on the list in sheet Criteria, but by code.

I was wondering if code could loop through the list
so I didn't require the lengthy code for individual criteria items

Sorry, I'm still not quite understand how is your situation.

Is it something like this ?

You already have a code with input box.
This input box is to be inputted the currency name (ARS, USD, CNY, etc).

loop through the list

Then the code loop to each list to check whether the inputted currency is in the list on sheet Criteria.

For example the user input "USD" into the input box.
If the code found "USD" in list of sheet Criteria,
then the code do the filtering process (filter sheet Currencies with criteria USD), and the copy/paste process.

Please correct me if I'm wrong.

[philwaters]

The query is this:

Show me the rows that contain ARS and copy them to a sheet named ARS.

Next, show me the rows that contain EUR and copy them to a sheet named EUR.

Next, show me the rows that contain PLN and copy them to a sheet named PLN.

And so on.

Each one of those tasks is covered by code that does all of the above but for each of the currencies so the example spreadsheet would have 10 different pieces of code which is identical apart from the currency code (ARS, USD, PLN, EUR etc)

This is manageable for 10 but I have over 110 currencies to get through and typing it all out takes forever.

I believe there must be one single piece of code that allows the user to jump through the currencies one by one, so basically this:

Filter the currencies worksheet by column B using criteria in criteria worksheet. So the first filter will be ARS. Currencies spreadsheet will then display only ARS results. All visible rows will be copied and pasted to a worksheet named ARS.

Once done the code moves to the next currency on the criteria sheet which is AUD. The currencies spreadsheet will filter by AUD and copy and paste the visible rows to a sheet named AUD.

And son on until no more rows exist on the criteria list.

All of this is successful but only when I have individual code listing the task for each currency. That means I have over 100 or so Subs

I want this condensed to a looping tasks if possible.

I hope this makes sense.

[karmapala]

The query is this:

Show me the rows that contain ARS and copy them to a sheet named ARS.
Next, show me the rows that contain EUR and copy them to a sheet named EUR.
Next, show me the rows that contain PLN and copy them to a sheet named PLN.

I'm sorry, it seems it's different than your original question ?
Because I don't see in your original question you mentioned that you want the copied filtered result is to be paste to a new sheet on each currency.
From the quote above now I understand why you need to loop, because each currency will have it's independent sheet with the respective currency code name.

Anyway, maybe this code :

Please Login or Register  to view this content.

The code will make a new sheet according how many items are in the list on sheet Criteria.
For example, if there are 4 currency codes in the list (sheet Criteria), AUS AUD CNY USD ...
then it will create four new sheets where each name of the sheet is AUS AUD CNY and USD.
Each of those sheet contains the filtered result pasted by the code after the code filter sheet Currencies column B then copy the result.

If the total currency-codes in the list of sheet Criteria will always be the same to the total unique value (of currency-code) in column B of sheet Currencies,
then I think the code doesn't "really" need the list in sheet Criteria, as it can be done by collect the unique value (of currency-code) in column B of sheet Currencies.
For example, the list in sheet Criteria has 10 currency-codes. This ten currency-codes is always the same of the total unique currency-code value in column B of sheet Currencies.

[philwaters]

Hi again

Thanks for taking more time to assist with this.

I didn't concentrate on the adding sheets part in my original post because I don't require assistance with this part.

Mainly I require assistance with the looping through each currency.

Everything else is already working fine.

I intend to try this out tonight but my immediate issue is that I do not require your code to include adding sheets. I will need to look at amending your code to remove this part. All I require is code that enables me to loop through each criteria and display each result one by one. I can add the copying and pasting etc.

[philwaters]

If the total currency-codes in the list of sheet Criteria will always be the same to the total unique value (of currency-code) in column B of sheet Currencies,
then I think the code doesn't "really" need the list in sheet Criteria, as it can be done by collect the unique value (of currency-code) in column B of sheet Currencies.
For example, the list in sheet Criteria has 10 currency-codes. This ten currency-codes is always the same of the total unique currency-code value in column B of sheet Currencies.

Not all the criteria in the list will display results at all times. I need to include instruction to deal with an error being encountered.

[karmapala]

my immediate issue is that I do not require your code to include adding sheets.
I will need to look at amending your code to remove this part. All I require is code that enables me to loop through each criteria
and display each result one by one.

Then I think you want the macro to something like "pause".

Maybe try to remove this line (the copy/paste line) :

Please Login or Register  to view this content.

and remove the create new sheet line :

Please Login or Register  to view this content.

then add this line (before the "Next" line) :

Please Login or Register  to view this content.

It will stop right after each filtering process on each currency-code,
then it gives you a message box, so you can see the filtered result first on sheet Currencies.
If you click Yes button in the message box, then it goes to the next criteria in the list of sheet Criteria, then stop again after the filtering process.
If you click No button in the message box, then it exit the sub.

[karmapala]

Not all the criteria in the list will display results at all times. I need to include instruction to deal with an error being encountered.

So, do you mean there can be a situation something like this ?

In sheet Criteria, the list has USD currency-code,
but there is no USD currency-code at all in sheet Currencies column B.

[philwaters]

USD is there in column B in Currencies in 6 occurences.

Not to worry though as your original coding is actually perfect for what I need, even adding the new sheets as I have thought of a way I can make this work and as it turns out it is better than what I am doing just now.

Serious thanks to you karmapala. I shall mark this as Solved.