skip to Main Content

by Matt Allington Today I am sharing a trick that I have used a number of times for clients – it allows you to use simple VBA to jump from one pivot table to another, and when you arrive at the second pivot table it is automatically filtered to show the context for the data you want to see.  It is a great user experience.

Here is a sample use case and demo

You are browsing and drilling into a product hierarchy looking at the change in performance vs last year.  You drill down to the product level and want to see the weekly sales for that particular product.  You simply click on the “show detail” button and you are taken to a detail page that shows you the details for the specific product you were looking at.  jump with context   Let me share the process of how to build this interactive report.

Use the VBA Recorder to do the heavy lifting

After I set up my 2 Pivot Tables, I recorded a couple of steps with the VBA Recorder to help me write the code.  Here is the process I followed.

  1. I selected the Summary Tab
  2. Turned on the Recorder
  3. Selected the Detail Tab
  4. Selected Any Year in the Pivot (doesn’t matter which one)
  5. Selected Any Product in the Pivot
  6. Selected the Summary Tab
  7. Turned off the Recorder

This recorded all of the steps I needed to use for my 2 macros.  This is what I got “out of the box”

Sub Macro1()
'
' Macro1 Macro
'
Sheets("Detail").Select ActiveSheet.PivotTables("PivotTable1").PivotFields( _
   "[Calendar].[CalendarYear].[CalendarYear]").ClearAllFilters ActiveSheet.PivotTables("PivotTable1").PivotFields( _
      "[Calendar].[CalendarYear].[CalendarYear]").CurrentPageName = _

"[Calendar].[CalendarYear].&[2003]" ActiveSheet.PivotTables("PivotTable1").PivotFields( _
   "[Products].[ProductName].[ProductName]").ClearAllFilters ActiveSheet.PivotTables("PivotTable1").PivotFields( _
      "[Products].[ProductName].[ProductName]").CurrentPageName = _
      "[Products].[ProductName].&[AWC Logo Cap]" 

Sheets("Summary").Select 

End Sub

I then set about manually adding some code to harvest the starting values from my first Pivot Table so I could pass these values through the code into the second Pivot Table.  I deleted the code in yellow as it is not needed, and changed the green sections to receive the selections from the first pivot on the Summary Tab.

change code

After making a few changes, this is what I have image

Note how I have added code at the top to “harvest” the selected values from the summary Pivot Table and then changed the original code to use these new values.  The red section picks up the selected year from cell D1, which contains a cube formula showing the selected year, and the green code reads the active cell.  The user just selects the product they are interested in before they run the macro. I then added a bit of error trapping code to in case the user has selected something other than a single product before running the macro.  Here is the final code.

Sub ShowDetail()
    On Error GoTo Error:
    myYear = Range("D1").Value
    myProduct = ActiveCell.Value
    
    Sheets("Detail").Select

    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Calendar].[CalendarYear].[CalendarYear]").CurrentPageName = _
        "[Calendar].[CalendarYear].&[" & myYear & "]"

    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Products].[ProductName].[ProductName]").CurrentPageName = _
        "[Products].[ProductName].&[" & myProduct & "]"
    GoTo ExitHere:
Error:
    Sheets("Summary").Select
ExitHere:
 
End Sub

Sub ShowSummary()
     Sheets("Summary").Select
End Sub

The last thing to do was to add a button on each page to run each of the macros image

And that’s it.  This provides a great interactive user experience.  The user doesn’t have to muck around manually changing tabs and re-selecting the products to find “more detail”.  The operation of the dashboard just disappears into the background and the user experience takes over.  You can download the sample workbook here.  Note I also used Dany’s excellent error trapping technique for time intelligence – read about that here

Matt Allington is a professional Self Service BI consultant and trainer based in Sydney Australia.

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. Visit Matt's blog here.

This Post Has 2 Comments
  1. Hi Matt, thanks for this post. I like “mixing” excel power to powerpivot to give the best end user experience. One question though here, what is happening if a user just selects “Bikes” and not a specific product before clicking the “show details” button? ideally it would still show the detailed graph for the sub category & not abort the action…. I guess the macro would need to be amended to react properly to various situations.
    Thanks, Claire

    1. Yes, I have deliberately set up this macro so it only responds to 1 scenario – an individual product. The line “On Error GoTo Error:” covers for all other scenarios (that will throw an error) and simply stops the macro and it exits gracefully. All the user sees is that it doesn’t work unless you select a product. You could make it more sophisticated as you describe, but that is probably beyond a “VBA 101” blog post.

      It is also worth noting that since you asked this question I have been thinking about having all the possible columns set up in the pivot as a filter. It then occurred to me that you could use the filtering features in the Field List instead of the Filter section of the pivot. This would mean that you can filter without having to put every column on the pivot table. Same process for recording the macro would still apply, but the code would be slightly different. You would then need to add some branching to catch which type of data is selected and apply the correct filter in the destination pivot

Leave a Comment or Question