Pivot Table, Looping through Filter

Hi Guys

First of all I'll hi, new here, been lurking a fair bit lately :-)

I'm working on a couple of macros at the moment and could do with some input regarding a specific issue, it seems simply but is probably complicated. (I'm not on my work comp right now so I'll try and be as concise as I can without being able to upload an example for now).

I have a sheet which using an edited version of this: http://www.rondebruin.nl/win/s5/pdf.htm I'm using to produce and send customer statements. I have this working perfectly.

The statement itself populates based on a pivot table, (filtering by customer name brings up a list of invoices, which I use to lookup against for the rest of the date I need).

What I'm trying to do is have a separate macro do the following

Select the pivot-filter > select the first customer > run aforementioned macro > select the next customer & loop until the end of the list.

Whenever I try and record something to get an idea how to do it, the customer name section is hardcoded text i.e "customer abcd". Anyhelp would be greatly appreciated, I've been searching but everything i find seems overly complicated and not really what i'm after. If I can this working it will probably shave a couple of DAYs of my working month!


TLDL: i need a VBA to loop through a pivot table filter running a separate macro for each entry.

If i understand correctly you want it to filter continuously ....

you will need to have 2 loops,

the inner one loops the pivot filter until it matches with who you want,

the outer one changes the filter to the next option ...

Please Login or Register  to view this content.

thanks so much for the speedy response.

I'm not at work but I've put together a quick example example 001.xlsx (no macros just an example of how my data is layed out)

so I can get this far:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer Name"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer Name"). _
CurrentPage = "Alan"

Runothermacroxyz



Where its selecting "Alan" I need it to select from a list (data sheet A:A), then once the second macro has run start fresh with the second name on the list.

As the list is often 100s of names long a loop seems like the 'correct' way to do this.

Thanks Again!