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!

 

[PivotMeThis]

Hi Jerry,

I am trying to alter the code to "Freeze Panes" in the drill down reports. I'm not sure what to do to make it happen.

All the best to you and yours
Rhonda

 

[Jerry Sullivan]

I am trying to alter the code to "Freeze Panes" in the drill down reports. I'm not sure what to do to make it happen.

Hi Rhonda,

If you revise the two lines as shown below, it should freeze the top row.

Public Function Format_PT_Detail(tblNew As ListObject)
'---Called by Workbook_NewSheet; Passes ShowDetai table object
'---Uses Pivot Table's SourceData Property stored in Public sSourceDataR1C1
'--- to read apply NumberFormats in first row of SourceData to tblNew
    Dim cSourceTopLeft As Range
    Dim lCol As Long
    Dim sSourceDataA1 As String
    
    If sSourceDataR1C1 = vbNullString Then Exit Function
    sSourceDataA1 = Application.ConvertFormula(sSourceDataR1C1, _
            xlR1C1, xlA1)
    Set cSourceTopLeft = Range(sSourceDataA1).Cells(1)
    With tblNew
        For lCol = 1 To .Range.Columns.Count
            .ListColumns(lCol).Range.NumberFormat = _
                cSourceTopLeft(2, lCol).NumberFormat
        Next lCol
        'Optional to do additional formatting
        Call Format_Table(tbl:=tblNew, _
            rFieldFormats:=Sheets("Drill Down").Range("A1").CurrentRegion)
        tblNew.Unlist   'Optional: Converts Table to Standard Range
        Range("A2").Select
        ActiveWindow.FreezePanes = True 'Optional: Freezes Top Row
    End With
    sSourceDataR1C1 = vbNullString
    cSourceTopLeft = Nothing
End Function

 

[PivotMeThis]

Works like a charm Jerry.

You're so cool!



FYI: I left this line of code in place:

Public Function Format_PT_Detail(tblNew As ListObject)
'---Called by Workbook_NewSheet; Passes ShowDetai table object
'---Uses Pivot Table's SourceData Property stored in Public sSourceDataR1C1
'--- to read apply NumberFormats in first row of SourceData to tblNew
Dim cSourceTopLeft As Range
Dim lCol As Long
Dim sSourceDataA1 As String

If sSourceDataR1C1 = vbNullString Then Exit Function
sSourceDataA1 = Application.ConvertFormula(sSourceDataR1C1, _
xlR1C1, xlA1)
Set cSourceTopLeft = Range(sSourceDataA1).Cells(1)
With tblNew
For lCol = 1 To .Range.Columns.Count
.ListColumns(lCol).Range.NumberFormat = _
cSourceTopLeft(2, lCol).NumberFormat
Next lCol
'Optional to do additional formatting
Call Format_Table(tbl:=tblNew, _
rFieldFormats:=Sheets("Drill Down").Range("A1").CurrentRegion)
tblNew.Unlist 'Optional: Converts Table to Standard Range
Range("A2").Select
ActiveWindow.FreezePanes = True 'Optional: Freezes Top Row
.Cells(1).Select
End With
sSourceDataR1C1 = vbNullString
cSourceTopLeft = Nothing
End Function

Thanks!!!

 

[PivotMeThis]

I have run into another little snag.

In the drill down formatting table that you developed you had a property for color, but it's for interior color. I want one for font color, and I'm pretty sure I've got that part right.

Case "FontColor"
.Font.Color = sNewValue

But I can't figure out what numbers to use. I wanted to change some column headings to white and I tried 255255255 but that didn't work and I saw your example only had 8 digits (10092543).

I've looked online and in the help files and can't find anything that works.

The reason I want to change the font color in a couple headings is because I added all this code into an old workbook and for some reason all but two of the column headings have white font with a blue background. Those two have a black font and it looks like they should be more important but they are not. (but this could be handy in the future too!)

Thanks!!!

 

[Jerry Sullivan]

Rhonda,

This article has a good explanation of differences between .ColorIndex and .Color
http://msdn.microsoft.com/en-us/library/cc296089(v=office.12).aspx

Sometimes it's easiest to just use the macro-recorder to capture the value of a color selected using Excel's Color Picker dialog.

 

[PivotMeThis]

Thanks Jerry, I'll check it out.

It didn't occur to me to record a macro to get the color.

...duh!

 

[PivotMeThis]

Case "FontColor"
.Font.ColorIndex = sNewValue


Wow, so much simpler. I found the table that shows the 57 colors (more than enough for my purposes) and I'm going to keep that tucked away for future reference.

Thanks buddy! You're the BEST!

 

[ExcelSSAS]

Hello Jerry,

I'm connecting the Pivot table in Excel to a SSAS Cube and want to customize the column names displayed in the drilldown table. When connecting to SSAS, I can create a Drillthrough action in the cube to determine which columns to show up in the drilldown table in Excel, but I cannot change the column names. The default names of the drilldown details will display something like this, [$DimName].[AttributeName], and I just want them to display as AttributeName (without square parentheses). So I think my "Drill Down" sheet will look like this:

	A	B	C
[$Employee].[Employee Name]	Rename	Name
[$Employee].[Employee Phone]	Rename	Phone
[$Employee].[Employee Email]	Rename	Email

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="bgcolor: #00FFFF"]Data Source Header[/TD]
[TD="bgcolor: #00FFFF"]Property or Method[/TD]
[TD="bgcolor: #00FFFF"]Value[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

</tbody>

What specific changes to the three sections of codes shall I make?

I'm new to VBA and working on modifying the codes for a long time and still can't figure it out. Could you give me some clues?

Thanks in advance!
Serena

Hi Rhonda,

Recently I went looking for a solution to the problem of the drill down detail not retaining the number formatting of the source data.
There are a lot of threads on the web that describe the problem, but I couldn't find any solutions.

Here is some code that addresses the NumberFormat problem. As written, it doesn't convert the Table to a standard Worksheet Range; or copy other formatting like fill color.

If you like, those features could be added fairly easily though since the hard part was triggering the formatting when a new sheet was added for PivotTable drill down, but not triggering the formatting for a new sheet added by the user.

The set up requires copying code into three different code locations in the workbook, each identified below.

Paste into Sheet Code Module of Worksheet(s) with 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 Public string sSourceDataR1C1
    On Error GoTo ResetPublicString
    With Target.PivotCell
        If .PivotCellType = xlPivotCellValue And _
            .PivotTable.PivotCache.SourceType = xlDatabase Then
                sSourceDataR1C1 = .PivotTable.SourceData
        End If
    End With
    Exit Sub
ResetPublicString:
    sSourceDataR1C1 = vbNullString
End Sub

Paste into ThisWorkbook Code Module

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim tblNew As ListObject
    On Error Resume Next
    
    Set tblNew = Cells(1).ListObject
    If tblNew Is Nothing Then Exit Sub
    Call Format_PT_Detail(tblNew)
    Set tblNew= Nothing
End Sub

Paste into a Standard Code Module

Public sSourceDataR1C1 As String

Public Function Format_PT_Detail(tblNew As ListObject)
'---Called by Workbook_NewSheet; Passes ShowDetai table object
'---Uses Pivot Table's SourceData Property stored in Public sSourceDataR1C1
'--- to read apply NumberFormats in first row of SourceData to tblNew
    Dim cSourceTopLeft As Range
    Dim lCol As Long
    Dim sSourceDataA1 As String
    
    If sSourceDataR1C1 = vbNullString Then Exit Function
    sSourceDataA1 = Application.ConvertFormula(sSourceDataR1C1, _
            xlR1C1, xlA1)
    Set cSourceTopLeft = Range(sSourceDataA1).Cells(1)
    With tblNew
        For lCol = 1 To .Range.Columns.Count
            .ListColumns(lCol).Range.NumberFormat = _
                cSourceTopLeft(2, lCol).NumberFormat
        Next lCol
    End With
    sSourceDataR1C1 = vbNullString
    cSourceTopLeft = Nothing
End Function

Just ask if you want any help extending the formatting features for your purposes.

 

[Jerry Sullivan]

Hi Serena and Welcome to the Board,

I'll take a look at this tomorrow when I'll have access to an OLAP cube source.

 

[ExcelSSAS]

Thanks Jerry! To give you more details on what I did - I changed .PivotTable.PivotCache.SourceType from xlDatabase to xlExternal, within the Sub Worksheet_BeforeDoubleClick. But the code has some connection issues, after the IF-THEN statements, the code goes to ResetPublicString.

Let me know if you need more clarification.

Serena