Pivot Table Drilldown - formatting, hidden columns, range instead of table

[PivotMeThis]

I find it highly annoying that Excel puts my data in a table with a bunch of filters when I drilldown in a pivot table. I always change it back to a range and make multiple changes to the formatting. If I have hidden columns in the base data they are no longer hidden in the worksheet which is created through the pivot table. It would be VERY nice if the new worksheet would be formatted like the base data...

Are there any advanced features I am missing to customize how the worksheet which is created by drilling down in a pivot table will appear? I have multiple workbooks that will be used by management and the data that is generated by the drilldown should appear formatted and ready for their use.

I thought about recording a macro to format everything and assigning it to a button but since the drilled down report will appear on a new worksheet I don't know where to put it or how to make it work. There could be multiple worksheets created by drilling down.

Thanks for any help out there.
I'm using 2010 - they are using 2007. Gotta love THAT!

 

[Jerry Sullivan]

Here's some code that you can try. It works by going back to the datasource and filtering it to match the records that are shown in the drilldown. From there it copies the Visible records and pastes them to a new sheet (including formatting). This last step is optional and can be removed if you just want to bring the user back to the filtered source data.

The code needs to be copied into three separate VBA code modules in your workbook.
It assumes you have a field named "Chng #" in your data source and that the values in that field may be used as Unique IDs for the records.

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
 On Error GoTo ErrProc
 
 '--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
 ActiveSheet.Cells(1).Activate
 Set rDrillDown = ActiveCell.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:
 Exit Sub

ErrProc:
 MsgBox Err.Number & ": " & Err.Description
 Resume ExitProc
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 = "Chng #"
 
 '--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
      On Error Resume Next
      .AutoFilter.ShowAllData
      On Error GoTo 0
      
      '--apply filter- split/join used to cast numbers as string
      .AutoFilter Field:=vUniqueIdCol, _
         Criteria1:=sFilterValues, _
         Operator:=xlFilterValues
      Application.Goto .Cells(1), True
   End With
   
   '--optional: Delete worksheet with drill down data
   Application.DisplayAlerts = False
   rDrillDown.Parent.Delete
   Application.DisplayAlerts = True
   
   '--optional: Copy visible data to new sheet
   Dim wsTarget As Worksheet
   Set wsTarget = Worksheets.Add
   With rPivotSource
      .Parent.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy
      wsTarget.Cells(1).PasteSpecial Paste:=xlPasteColumnWidths
      wsTarget.Cells(1).PasteSpecial Paste:=xlPasteAll
      
      '--optional: clear filters
      On Error Resume Next
      .AutoFilter.ShowAllData
      On Error GoTo 0
   End With
   
 Else
 '--code could be extended to add UniqueIDs or Match Keys.
   MsgBox "Header: " & sUNIQUE_ID_HEADER & " not found."
 End If

ExitProc:

End Sub

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

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

One note of caution, if the PivotTable's data source has been changed and the PivotTable has not been refreshed, this could yield unexpected results because the drilldown data may not match the source data.

 

[acesRwild]

Hi Jerry!

I've been reading through all of the posts related to Pivot Drilldowns and hiding columns from the source data in the drill view. Your posts seem to be very close to offering the solution that I am seeking, however I have a single workbook with 2 data sources and multiple pivots. I'm hoping that you might have a solution for me as well? Following are my questions:

1. Could I use this function in a workbook that contains 2 different data sources for 3 different pivot tables?

• My budget/actual table feeds two pivots while my actual table feeds another.
• All three pivots should have a "skinny" view of the source data when the user drills to the GL detail.
• I imagine that I would need 2 separate "lstFieldstoView" (with unique names), however I do not know where to place the code to "tie" it to each respective spreadsheet.
• I don't believe that it should matter, but my source data tables are ODBC linked.

2. On the third piece of code in your examples, you advise to Paste into "Standard Code Module". Where exactly is that?

• I have 4 modules listed under the "Modules" folder. Should I be placing it there somewhere?

I truly appreciate any direction that you might be able to provide.

Thank you!
Angela

 

[Jerry Sullivan]

Hi Angela,

#1. Yes, this could be applied in workbooks with multiple pivot tables and pivot caches.

How to do it depends on whether the fields to be kept are the same for each pivot table (whether the pivot's source data is the same doesn't matter). The code works by stepping through each field in the drill down and deleting the field if it is not in your "lstFieldstoView" named range (the code example used "lstFieldstoKeep").

If fields are the same, you could do that with just one named range list "lstFieldstoView". In fact if the fields are completely different in each pivot's drilldown you could use one named range list "lstFieldstoView" that listed all the unique field names to keep -if found- in each pivot's drilldown.

Things get a more complex if there are field names that should be kept in one pivot's drilldown and deleted in another. For that it's probably easiest to have separate named ranges for each combination. You'll need to establish some way to distinguish the two or more drilldown datasets and map them to the corresponding named range list.

For example the code could test for the presence of field that will be unique to that drilldown then use a specific named range list.

If you'll explain which of the above scenarios applies to your workbook, I'll suggest some code.

#2. The modules listed in the VB Project Explorer under the "Modules" folder in your workbook are what I was referring to as "Standard" Code Modules. I'm not positive to which code example you are referring since there are several variations in this thread. (I'm guessing post #40, with modification in #42- but that only had two pieces of code not three.

 

[MOSHE]

Hi Jerry
I have to apply conditional formatting to a column in a drill-down table. I'm not profficient in VBA and do not undertand how I can use the code you posted above. Any help will be really appreciated. Thanks in advance

 

[Jerry Sullivan]

Hi MOSHE,

If you'll provide more details about what you are trying to do, I'll try to help.

Use the macro recorder while manually going through the step of applying the Conditional Formatting (CF) to that field, then post what is recorded.

Also provide the name of the field to which the CF is to be applied.

 

[Saifox]

Hi there,

Thank you very much, this thread has helped me to filter duplicates out of my drill down results.

I wanted to ask how I would go about deleting columns from the drill down results if the source for the pivot table is a POVERPIVOT data model. Is this possible?

Also is there a way I can automatically convert a column containing URL text to actual URL links?

Cheers

 

[Jerry Sullivan]

Hi Saifox,

The original purpose of this code was to apply the same number formatting used in the PivotTable data source range to the drilldown. For that reason the code quickly exits if it finds that the data source is not an Excel workbook range.

The other parts of the code (that deletes/renames fields or applies custom-defined formatting) could be modified to work with PivotTables that have PowerPivot Data Models with some adjustments.

There seems to be enough interest in this thread that I think, I'll take a pass at rewriting the code to handle PivotTables with data sources other than Excel worksheet ranges.

It may take me a few days to get to that. If you are wanting something sooner, you might try adapting the code in this thread (also initiated by Rhonda):

http://www.mrexcel.com/forum/excel-...row-if-cell-contains-n-remove-duplicates.html

That worked for me when I tested it on a PivotTable with a PowerPivot Data Model source.

For converting URL's you could use code like this.

Sub ConvertUrlsToHyperlinks(tbl As ListObject, _
   sFieldName As String)

'--converts URL strings in the specified field of a table
'    into hyperlinks. Does not check whether URL is valid.
   
 Dim rCell As Range
 Dim sURL As String
 Dim lcoListCol As ListColumn
 
 On Error Resume Next
 Set lcoListCol = tbl.ListColumns(sFieldName)
 On Error GoTo 0
 If lcoListCol Is Nothing Then Exit Sub
 
 For Each rCell In lcoListCol.DataBodyRange
   sURL = LCase$(rCell.Value)
   
   '--ignore blanks
   If Len(sURL) Then
   
      '--prepend protocol if missing
      If Left(sURL, 4) <> "http" Then
         sURL = "http://" & sURL
      End If
        
      ActiveSheet.Hyperlinks.Add Anchor:=rCell, _
         Address:=sURL
   End If
 Next rCell

End Sub

That Sub could be called from the Sub CheckDrillDown in the thread I referenced like this...

Call ConvertUrlsToHyperlinks(tblNew, sFieldName:="Websites")

 

[rolandas199]

Hello,

I do have in the source data the below format in the I, J, K, L, M columns:
Sheets("DATA").Range("I:M").NumberFormat = "mm/dd/yyyy hh:mm am/pm"

When I make a pivot table of this Data sheet in a sheet Summary and go to drill down, filter the Pivot Table, these columns appear as 24HH/MM format. I would like to have those in am/pm format. I have tried to change the drilldown options but it does change for the whole spreadsheet, so that is not an option.

Can someone advise and help me on this? Thank you very much!

 

[Jerry Sullivan]

Hi rolandas199,

The code could be adapted to specifically apply that number formatting to Columns I:M of the Drilldown sheet, but that approach has some drawbacks. Anytime you add or delete a column to your data source before column I, you'll need to edit the VBA code to adjust those columns. You'll also have a problem if you add another PivotTable in your workbook that has a difference data source range.

I'd suggest you try the code from Post #2 of this thread which applies the formatting in each field of the data source range to the corresponding field of the drilldown range. If that works for you it should require less maintenance.

 

[rolandas199]

Hello Jerry,

thank you works like a charm! However I noticed one thing, that for example for me it shows for example "1/4/2016 12:00AM" and for my collegue shows just the date, even though it is the same file, and the same source. What are the reasons of this, maybe you happen to know? Thank you very much!