I 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|
|Ans2 = MsgBox("Would you like to refilter pivot tables", vbYesNo)|
|Select Case Ans2|
|'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.EnableMultiplePageItems = False|
|PF.CurrentPage = Ans|
|MsgBox ("Remember to re-filter before closing")|
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?