macro to use all possibilities of a filter

[collierd]

Hello

I have a macro that copies part of the contents of a spreadsheet to another worksheet and reformats this.
What it copies is based on a filter using:

Selection.AutoFilter Field:=1, Criteria1:="2"

Where Criteria1 is specifying a numeric branch

We have around 60 branches and these are intermittant between 1 and 1000

Basically, I want to put this part of the macro into a subroutine and call the subroutine using the branch, resulting in 60 worksheets automatically created and formatted.

How do I loop through a filtered column
Is there a way I can send a unique branch set to an array to loop through (maybe?)

Thanks

Damian.

 

[misterstick]

amazingly, excel will do this for you, you don't need VBA.

look into PivotTable:ShowAllPages.

good luck.


mr s. <

 

[collierd]

I've looked at pivot tables and couldn't come up with an answer

The problem is, I need the top 10 and bottom 10 rows (order criteria will be specified on the originating table)

So I am cutting and pasting into a new worksheet then cutting out everything bar the top and bottom 10 then reformatting the page to be presentable

Is all this possible via pivot tables?

 

[misterstick]

you can use a cell formula to get "top/bottom 10" (if column A contains rank) and we're in row 1:

=IF((MAX($A:$A)-$A1)<10,"Bottom 10",IF($A1<=10,"Top 10", "Meh"))

you can then select on this field in whatever table you like.


mr s. <

 

[xlbo]

If you want to use filter, you can use ADVANCED filter to create a list of unique values. You may then loop through that list, applying each value in turn to the autofilter

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Here is an example of that ...

http://www.mrexcel.com/board2/viewtopic.php?t=120587&highlight=autofilter+advancedfilter

(page 2 has an explanation of the code)

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[collierd]

Thanks

That mrexcel post looks fantastic
I will have a good look through it

Damian.