Modify PivotTable double-click behavior --OR-- ...

[gr8tday]

Hello, I need some VBA code to address the following:

Scenario: A pivot table reveals a probable error in the underlying source data. The error needs to be located and corrected in the source data, and then the pivot table needs to be refreshed.

*Problem*: The double-click action on a pivot table fields creates a new worksheet with the records applicable to that field. A user must *identify the data error in the new worksheet and then scour the source data to find that same record*, correct the data error in the source, and refresh the pivot table.

One of two alternatives would make this process much easier for the user:

Option 1: Use VBA to modify the behavior of a PivotTable cell's double-click action to return the user to the source table, and automatically apply filters to the source data to display only those the records applicable to that field. (Assume the source data is already formatted as a Table.) In other words, don't create a new sheet with the applicable data, just filter the source data to show the applicable data. Then the user can find and correct the problem in one shot - in the source data.

This option would mean the user would not have to manually delete the new worksheet when they are done with it - always an annoyance. However, I don't want to affect the new worksheet functionality to achieve this goal; I've seen other code for the PivotTable double-click function affect this functionality.

Option 2: Allow the PivotTable double-click to work as normal. When the user finds the culprit record in the new worksheet, automatically take the user to that same record in the source table via a a macro short-cut key.

Thank you for any assistance you are able to provide!

 

[mole999]

i think what you are proposing is not possible on a pivot, the drill down is about showing the detail. That said when you have located and fixed the problem there is no reason you can't have excel tidy you additional sheets away to avoid clutter (just make sure no needed sheet is called sheet)

 

[Jerry Sullivan]

The default double-click behavior can be canceled through event code and the user can be redirected to a range assigned to the SourceData property.

The more difficult part is filtering the SourceData range to show only those records that would have been generated by the drilldown.

@gr8tday if you have some experience with VBA, the code in these two links could help you get started.

This thread had similarities to your Option 1 except the user was getting drill down from a PivotChart.
http://www.mrexcel.com/forum/excel-...able-source-data-rather-than-showdetails.html

This thread has similarities to your Option 2, except the user wanted to edit the drill down and have the source data be changed through VBA.
http://www.mrexcel.com/forum/excel-...e-help-table-generated-pivot-table-excel.html

The approach I'd suggest for you would be to combine aspects of those two examples:

1. Allow the Drill down data to be generated.

2. If source data has a field with Unique ID's, use that field of the records in the drill down to apply filters to the source data. Then delete the drill down sheet and display the filtered data for the user.

3. If the source data doesn't already have Unique ID's, the VBA code could generate keys that concatenate all the field values to find matching records.

 

[gr8tday]

Thanks Jerry, I will look into your suggestions. The idea of a unique key sounds very promising and useful in a number of ways; I will definitely add one to the data. My VBA experience is limited to google searches and patching code from various sources together. I can usually accomplish what I need, but I'm sure it would make VBA programmers cringe!

 

[Jerry Sullivan]

Just ask if you want some help with the coding.

One caveat is that if the values in the source data range have been modified since the last time the pivot was refreshed, the drill down records may not match those in the current source data range.

Ideally the pivot would be refreshed prior to the user double clicking a pivot cell. If the source data is on a separate sheet, one way would be to refresh the pivot anytime the sheet with the pivot is activated.

 

[gr8tday]

Jerry,

Yes, I would greatly appreciate some help with the code. I have Googled for a few hours and have not been able to come up with a solution. I've added a unique key to my source data (sheet name = Data). I've got a pivot table based on the table in Data. I drilled on a subtotal of the pivot table, and that resulting sheet and table are what I am now trying to add a hyperlink to. Below is the table created by the pivot table drill and my desired result - hyperlinks on the Index column (my unique key).

Column H will not normally exist - it's just there while I figure out the macro. I added a formula to retrieve the address of the same record in the Data sheet. That is the SubAddress I need for the hyperlink in A2. My formula for H2 is: =ADDRESS(MATCH(Table57[@Index],Data!A:A,0),1)

I'm stuck on how to incorporate that formula into the macro. There are two issues to address:

1. I need the macro to calculate the ADDRESS(MATCH value and then use it in the hyperlink SubAddress. (I believe I need to create that value within the For Each section of the macro and then use it in the hyperlink SubAddress.)

2. The table name in the formula (Table57) will not be static. The pivot table drill will create a differently named table every time. So in the macro, the ADDRESS(MATCH formula will need to refer to the table in the active sheet rather than a table name.

So far, my code looks like:

Sub CreateHyperlinks()

Dim myRange As Range
Dim Cell As Range


Call SelectColumn
Set myRange = Selection


For Each Cell In myRange
    Excel.ThisWorkbook.ActiveSheet.Hyperlinks.Add Anchor:=Cell, Address:="", SubAddress:="Data!" & Cells(2, 8).Address
Next Cell


End Sub

I would greatly appreciate any help you are willing to offer!

 

[Jerry Sullivan]

Are you sure that you want to use hyperlinks?

I liked your idea of having Excel go to the worksheet that has the data source, then using autofilter to show the same records that are shown in the drill down. Allowing the ability to edit the data source directly seems like a better user-interface.

Do you want help writing that code or is there some reason you prefer hyperlinks?

 

[Jerry Sullivan]

Here's some code you can try that should work if your source data has unique ID's in a field with a header "Index".

The code needs to be copied into three separate VBA code modules in your workbook.

Paste this into the ThisWorkbook Code Module...

Private Sub Workbook_NewSheet(ByVal sh As Object)
 Dim sPivotSourceR1C1 As String
 Dim rDrillDown As Range
 
 '--check if this new sheet is a result of drill down data
 '  generated by double-clicking PivotTable that is
 '  "watched" by worksheet_dblclick event. if so,
 '  the Pivot's SourceData property was stored in global var
 
 If gsSourceDataR1C1 = vbNullString Then GoTo ExitProc
 
 '--store value then clear global variable. This ensures
 '  gotoSourceData sub won't be triggered unintentionally later
 sPivotSourceR1C1 = gsSourceDataR1C1
 gsSourceDataR1C1 = vbNullString

 '--get the drill down range so it can be passed to sub
 Set rDrillDown = ActiveSheet.Cells(1).CurrentRegion
 
 '--call sub that will goto saved source data range and filter it
 '  to show just the drill down records
 Call GoToSourceData(rDrillDown:=rDrillDown, _
      sPivotSourceR1C1:=sPivotSourceR1C1)
 
ExitProc:
 Set rDrillDown = Nothing
End Sub

Paste this into a Standard Code Module...

Option Explicit

Public gsSourceDataR1C1 As String   'temporarily stores R1C1 reference to
                                    'the source data range of dbl-clicked
                                    'pivot. Is quickly cleared by Workbook_NewSheet
                                    

Public Sub GoToSourceData(ByVal rDrillDown As Range, _
      ByVal sPivotSourceR1C1 As String)
      
'--this sub will goto the pivot source data range then
'  use autofilter to show only the records in rDrillDown
'  if Unique ID header is not found, no action is taken
'  code could be extended to add UniqueIDs or Match Keys.
 
 Dim lNdx As Long
 Dim rPivotSource As Range
 Dim sFilterValues() As String
 Dim vUniqueIdCol As Variant
 Dim vUniqueIdVals As Variant
 
 Const sUNIQUE_ID_HEADER As String = "Index"
 
 '--find unique id field in header of drill down, if it exists
 vUniqueIdCol = Application.Match(sUNIQUE_ID_HEADER, rDrillDown.Resize(1), 0)
 If IsNumeric(vUniqueIdCol) Then
   '--read all unique id's
   vUniqueIdVals = Application.Index(rDrillDown, 0, vUniqueIdCol)
   
   ReDim sFilterValues(LBound(vUniqueIdVals, 1) To UBound(vUniqueIdVals, 1))
   
   '--convert to 1D Array and cast values as strings for autofilter
   For lNdx = LBound(vUniqueIdVals) To UBound(vUniqueIdVals)
      sFilterValues(lNdx) = vUniqueIdVals(lNdx, 1)
   Next lNdx
   
   '--goto source data- this will throw error if source is in a
   '  external workbook not open in the workbooks collection
   On Error Resume Next
   Set rPivotSource = Range(Application.ConvertFormula( _
      sPivotSourceR1C1, xlR1C1, xlA1))
   Application.Goto rPivotSource
   If Err.Number <> 0 Then
      On Error GoTo 0
      MsgBox "Unable to access PivotTable data source at: " _
         & sPivotSourceR1C1
      GoTo ExitProc
   End If
   
   With rPivotSource
      '--clear any existing filters
      .Parent.FilterMode = False
      '--apply filter- split/join used to cast numbers as string
      .AutoFilter Field:=vUniqueIdCol, _
         Criteria1:=sFilterValues, _
         Operator:=xlFilterValues
      .Cells(1).Select
   End With
   
   '--optional: Delete worksheet with drill down data
   Application.DisplayAlerts = False
   rDrillDown.Parent.Delete
   Application.DisplayAlerts = True
 Else
 '--code could be extended to add UniqueIDs or Match Keys.
   MsgBox "Header: " & sUNIQUE_ID_HEADER & " not found."
 End If

ExitProc:
 Set rPivotSource = Nothing
End Sub

Paste this into the Sheet Code Module of the Sheet(s) that have the PivotTable(s).

Private Sub Worksheet_Activate()
'--refreshes pivottable each time sheet is activated
'  to ensure that drilldown records are always
'  in sync with data source

   Dim pvt As PivotTable
   
   For Each pvt In Me.PivotTables
      pvt.PivotCache.Refresh
   Next pvt
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
'---If user double-clicks in PivotTable data, assigns a string reference to
'---  the Pivot Table's SourceData Property to global variable gsSourceDataR1C1

    On Error GoTo ResetPublicString
    With Target.PivotCell
        If .PivotCellType = xlPivotCellValue And _
            .PivotTable.PivotCache.SourceType = xlDatabase Then
                gsSourceDataR1C1 = .PivotTable.SourceData
        End If
    End With
    Exit Sub
ResetPublicString:
    gsSourceDataR1C1 = vbNullString
End Sub

 

[gr8tday]

Hi Jerry!

Thank you so much for spending time on this! I too like the idea of going directly to the source data (filtered) from the pivot table double-click, but that was well beyond my own capabilities. The hyperlink seemed that it might be within my reach.

I am excited about your solution, but I'm not seeing any difference in the behavior of the pivot table double-click. I deleted all existing code from the workbook. I pasted the first code set into ThisWorkbook, the second code set into Module1 and the third set into Sheet2(Pivot). When I double-clicked the pivot table in Pivot, a new sheet was created with the table range selected - the usual effect of the pivot table double-click. I also tried saving, closing and reopening the workbook.

I double-checked my Index column - no weird things, extra spaces or anything in the column name of Index. It is formatted as a table. I see that message boxes that will present the user with errors are in your code, but I'm not getting any of those.

It seems like the double-click action is not calling the macros at all. Suggestions?

 

[gr8tday]

I should have mentioned up front: I'm using Excel 2010, Win 7 on a 64-bit OS.