How do I re-filter only selected Pivot Tables in my workbook?

true storyI had an interesting case a couple of years back, which resulted in quite a bit of head-scratching on my part.

I was working as a reporting analyst and one of my key deliverables was to produce a monthly analysis of the hours that our consultants had logged on different projects. The analysis was to have a dual purpose – on one hand it was supposed to provide an indication of the earning potential of the billable projects for our business unit. On the other hand, it served as a workforce management input to inform the allocation of the so-called “shared consultants”. As by definition the hours that those “shared consultants’ logged were absorbed as a cost by HQ, they had to be nullified when assessing the billable projects, yet be visible in the workforce management views.

This in turn meant that:

a) I could not re-state the hours and cost associated with those consultants in the MS Access database that was feeding the pivot tables in my spreadsheet

b) In order to show a correct view I had to:

  • refresh all the data in my spreadsheet, so that the latest fiscal period was available
  • re-state the hours and costs associated with the “shared consultants”
  • re-filter a couple of pivot tables after the values were re-stated to be showing only the data for the past month

Therefore, it seemed reasonable at the time that the way to tackle the challenge would be to have a couple of  VBA routines: 1 to refresh all data, 1 to be re-stating the values and a 3rd one, which would re-filter the set of tables in the end (you can see its code below)

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim Wks As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim Ans As String
Dim Ans2 As Integer
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva 2013
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Worksheets("CostsAnalysis").Activate
Ans2 = MsgBox("Would you like to refilter pivot tables", vbYesNo)
Select Case Ans2
Case vbYes
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'apply filter for the latest month to all pivot tables
Ans = InputBox("Please enter latest fiscal period in the format Period nn yyyy")
For Each Wks In ThisWorkbook.Worksheets
If Wks.PivotTables.Count > 0 And Wks.Name <> "Presales Costs" _
And Wks.Name <> "Costs Trend" And Wks.Name <> "# Details" Then Wks.Activate
For Each PT In ActiveSheet.PivotTables
Set PF = PT.PivotFields("Fiscal year/period")
On Error Resume Next
PF.ClearAllFilters
PF.EnableMultiplePageItems = False
PF.CurrentPage = Ans
Next PT
Next Wks
Case vbNo
MsgBox ("Remember to re-filter before closing")
End Select
End Sub

Looks cool but why didn’t you use slicers to re-filter your pivot tables?

Those are much more user friendly and intuitive to handle.

That is true! Slicers, however, only work for pivot tables that share the same pivot cache. The tables that I had to re-filter did not – the ones that were assessing the different projects’ earning potential were pulling data directly from the MS Access database via an OLE DB connection, whereas the ones that had to provide input for the workforce managers’ decisions were based on a spreadsheet-based table which had the correctly restated costs and hours.

Because of the differences in pivot caches between the pivot tables I opted for the VBA approach. In hindsight, there might have been a sleeker way of tackling this challenge

I am curious, however – how would you have handled it?