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):
Sub Filter()
Dim Worksheet
Dim PivotTable
'loop 1 works through all worksheets
For Each Worksheet In Worksheets
'not sure I need this, can I instead change ActiveSheet.PivotTables to Worksheet.PivotTables later on in loop2?
Worksheet.Select
'loop 2 works through all pivot tables in each worksheet
For Each PivotTable In ActiveSheet.PivotTables
'this bit only here to display details of code progress for my benefit
MsgBox PivotTable.Name & "/" & Worksheet.Name & "/" & ActiveSheet.Name
'alter page item filter, this is the bit that seems to go *very* wrong
PivotTable.PivotFields("ORG_NAME").CurrentPage = "Organisation1"
'close loop 1
Next PivotTable
'close loop 2
Next Worksheet
End Sub
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:
PivotTable.PivotFields("ORG_NAME").CurrentPage = _
"Organisation1"
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
Bookmarks