VBA Pivot Table Filtering

Hello all, I'm getting a bit frustrated as I'm no VBA ninja. I'm trying to do something that feels like it should be really simple but I'm finding it quite tough.

I'm using Excel 2007 and have a .xlsm workbook that contains several pivot tables on different worksheets. Each pivot table links to the same access database and just displays the results of queries to make an easy to print report.

The underlying data has a field which lists organisation names and I would like to be able to run some code which would loop round each pivot table in the workbook and filter each one to display information relevant to each organisation.

Ultimately I also want the code to print the report after it's done this but one step at a time, for now I just want to get the filtering side of things working.

Here's how I want it to work eventually:

1: Filter pivot table1 to display Organisation1 --> Filter pivot table2 to display Organisation1 --> Print
2: Filter pivot table1 to display Organisation2 --> Filter pivot table2 to display Organisation2 --> Print
3: Filter pivot table1 to display Organisation3 --> Filter pivot table2 to display Organisation3 --> Print

So, here is where I am thus far (sans printing, just want the nuts and bolts to function right now):

Please Login or Register  to view this content.

If I comment out the row that sets the filtering up then the code seems to work fine (based on the text seen in the message box).

I have seen other examples where a bit of code along the lines of 'i = 1 to Worsheets.count' in order to generate the loop, not sure which method is better but the main problem seems to be around setting the pivot table filter. I have also tried using the following to no avail:

Please Login or Register  to view this content.

I hope I'm not making a completely amateurish mistake but suspect I might be.

Almost forgot, the actual error message I get is "Error '1004': Unable to get the PivotFields property of the PivotTable class".

Thanks all

Si