Autofilter each item in a list

How can I loop through each item in an autofilter list?
I am sending data from Access then need to filter and copy to individual
sheets. The filter column will contain different data everytime. I thought
about copying the unique values to a range on the side and looping through
that as Criteria1. I was hoping there was a way to loop through the dropdown
directly.

there isn't. You approach is reasonable. Another way to build a list of
uniques is to use a collection or dictionary.



The general approach of using a collection is demo't at John Walkenbach's
site:

http://www.j-walk.com/ss/excel/tips/tip47.htm

You could adapt it to your problem.

--
Regards,
Tom Ogilvy


"Chip" wrote:

> How can I loop through each item in an autofilter list?
> I am sending data from Access then need to filter and copy to individual
> sheets. The filter column will contain different data everytime. I thought
> about copying the unique values to a range on the side and looping through
> that as Criteria1. I was hoping there was a way to loop through the dropdown
> directly.

Thanks,Tom. I'll adjust that code then proceed with my looping.

"Tom Ogilvy" wrote:

> there isn't. You approach is reasonable. Another way to build a list of
> uniques is to use a collection or dictionary.
>
>
>
> The general approach of using a collection is demo't at John Walkenbach's
> site:
>
> http://www.j-walk.com/ss/excel/tips/tip47.htm
>
> You could adapt it to your problem.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Chip" wrote:
>
> > How can I loop through each item in an autofilter list?
> > I am sending data from Access then need to filter and copy to individual
> > sheets. The filter column will contain different data everytime. I thought
> > about copying the unique values to a range on the side and looping through
> > that as Criteria1. I was hoping there was a way to loop through the dropdown
> > directly.