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]

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.

 

[PivotMeThis]

Hi Jerry,

Wow and thank you. I don't really understand how this code works or what I need to do to alter it for my purposes. It looks like any changes need to be made to the third code location? But I don't know how to write code to hide columns and format. Can I write a macro and then copy that code into it?

What if I have multiple pivot tables in a workbook? Will it still work? Will all the tables need to be formatted the same way? This seems logical to me.

Thanks again for your help - I really appreciate it.

 

[Jerry Sullivan]

What if I have multiple pivot tables in a workbook? Will it still work? Will all the tables need to be formatted the same way? This seems logical to me.

Rhonda, Starting with the questions above... It will work for multiple pivot tables in a workbook or worksheet provided that you paste the function "Worksheet_BeforeDoubleClick" into the Sheet Code Module of each Worksheet that has one or more Pivot Tables.

The Pivot Tables and Drill Down Tables don't need to formatted the same way, but it will be much simpler if the Drill Down Tables can all have the same rules applied to the formatting.

For example, if you want to hide all columns in the Drill Down Table that aren't currently shown in the Pivot Table, that would be fairly simple.
On the other hand, if you want to hide "Sum of Sales" and "Customers" for PivotTable1" and hide "Count of Sales" and "Vendors" for PivotTable2, that would be more complex and tailored.

Understanding that, perhaps you could describe whatever rules you'd like to apply to all Drill Down Tables. I'll take a first pass at modifying the code to do that.

 

[PivotMeThis]

These are the column headings from the report and which ones should be hidden, I also included a few things that require formatting. All drill down tables will have the same rules applied. Is it also possible to remove all the filters that are created when drilling down?

Designer
Letting Date (hide)
Office (hide)
Contractor (hide)
Project Number
Contract ID (hide)
Acctg ID (hide)
Project Awarded Amount (currency)
Project Authorized Amount (hide)
Project Paid to Date (currency)
Project Abstract Date (hide)
Item Number (hide)
(CPS) Project Line Number (text, it comes in the way it should)
Proposal Number (hide)
Contract Modification Number (text, it comes in the way it should)
Date Approved (hide)
Contract Modification Description (hide)
Item Description
Awarded Quantity (number, 3 decimals)
From Quantity (hide)
Amount of Change (hide)
To Quantity (hide)
Unit Price (hide)
Unit
Authorized Amount to Date (hide)
Contract Modification Amount (currency)
Item Qty to Date (hide)
Item Paid to Date (hide)
Item No Description (hide)
Status

Thanks again for your help!

 

[Jerry Sullivan]

Rhonda,

Here's an attempt to allow you or anyone else to adapt this with minimal editing of the VBA code.
To do that, I'd suggest the use of a three-column range within your workbook to define any special formatting.

This is loosely based on your list; however I've modified it a bit to show Delete and Color.

In this example, the range is on Sheet "Drill Down" which could be a hidden sheet if you prefer.

Excel Workbook
	A	B	C
1	Data Source Header	Property or Method	Value
2	Letting Date	Hidden	TRUE
3	Designer
4	Office	Hidden	TRUE
5	Contractor	Hidden	TRUE
6	Project Awarded Amount	NumberFormat	"$#,##0.00"
7	Project Authorized Amount	Delete
8	Awarded Quantity	NumberFormat	"0.000"
9	Awarded Quantity	Color	10092543
10	From Quantity	Hidden	TRUE
Sheet Drill Down

The order of the fields on the list doesn't matter and you don't need to list the fields that do not need special formatting (like "Designer").

You could list all your fields if it helps you- the code will make no change to a field if its second column is blank.

The only other setup needed is to replace function Format_PT_Detail with this version,
and paste the function Format_Table shown below into the same Standard Module as Format_PT_Detail.

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
        .Cells(1).Select
    End With
    sSourceDataR1C1 = vbNullString
    cSourceTopLeft = Nothing
End Function

Private Function Format_Table(tbl As ListObject, rFieldFormats As Range)
'---Uses the information in rFieldFormats to format the Table
'   3 columns in rFieldFormats define: Field | Property | New Property Value
'   Example Net Sales | NumberFormat | "$#,##0.00"
    
    Dim c As Range
    Dim sField As String, sFieldRef As String
    Dim sProperty As String, sNewValue As String
    On Error Resume Next
    
    For Each c In rFieldFormats.Resize(, 1)
        sField = c(1, 1)
        sProperty = c(1, 2)
        sNewValue = c(1, 3)
        sNewValue = Replace(sNewValue, """", "") 'remove any quotes
        
        sFieldRef = tbl.Name & "[" & sField & "]"
        With Range(sFieldRef)
            Select Case sProperty
                Case "Color"
                    .Interior.Color = sNewValue
                Case "Delete"
                    .EntireColumn.Delete
                Case "Font"
                    .Font = sNewValue
                Case "Hidden"
                    .EntireColumn.Hidden = sNewValue
                Case "NumberFormat"
                    .NumberFormat = sNewValue
                Case ""
                    '---No formatting changes
                Case "Property or Method"
                    '---Skip rFieldFormats Header Row
                Case Else
                    MsgBox sProperty & " is not a defined Property " & _
                        "or Method in Function Format_Table"
            End Select
       End With
    Next c
    Set c = Nothing
End Function

These two functions are unchanged and should be pasted where noted in in Post #2 of this thread.
Workbook_NewSheet
Worksheet_BeforeDoubleClick

Please let me know if this does what you wanted, or if you want any help adapting it for your use.

 

[PivotMeThis]

WoW Jerry! This is SO COOL!!! I can use this in lots of workbooks!!! Thanks so much!!!

I have entered my own range of formatting rules on a sheet called "Drill Down" and it works great.

I hate to ask for more, but is there a way to add more formatting, such as wrapping text and fitting column widths in the headings? Some of my headings are really long - such as "Contract Modification Number". Can I do something so that the heading row is 3 rows high, text is wrapped and columns are sized to fit? "Item Description" is another big one, but not the heading, the information for each item can be lengthy and I would like to wrap that column with a column width of 50.

I know I am looking at two different things here. If I can figure out how to add "Wrap" in the formatting rules I could apply it to the column for description but the formatting for the headings is another matter - right?

You have helped me so much I hate to continue with the questions but this is an exciting learning opportunity for me.

 

[Jerry Sullivan]

Rhonda,

I don't mind you asking - I'm happy that you plan to use this code for more uses and want to take it a step further.

I like your idea to have the option to format the Table Header differently than the Table Data range.

Perhaps one more column in the Drill Down formatting table to specify Header, Data or Both?

Excel Workbook
	A	B	C	D
1	Data Source Header	Table Part	Property or Method	Value
2	Letting Date	Both	Hidden	TRUE
3	Designer	Data
4	Office		Hidden	TRUE
5	Contractor	Both	Hidden	TRUE
6	Project Awarded Amount	Data	NumberFormat	"$#,##0.00"
7	Project Authorized Amount		Delete
8	Awarded Quantity	Data	NumberFormat	"0.000"
9	Awarded Quantity	Data	Color	10092543
10	From Quantity	Both	Boss Favorite
11	Contract Modification Number	Both	ColumnWidth	12
12	Contract Modification Number	Header	WrapText	TRUE
Sheet

Regarding WrapText and AutoFit, I've added those and some other examples to the code. Hopefully you can follow how those examples work and add your own by using the Macro-Recorder and pasting more Case: statements.

The "Boss Favorite" example is there to show the keywords don't have to be Excel Properties - they can be any keywords that you tie to the macro. That example also shows that you could edit multiple properties with one keyword.

Here's a modified version of the Format_Table functions. The other procedures are unchanged.

Private Function Format_Table(tbl As ListObject, rFieldFormats As Range)
'---Uses the information in rFieldFormats to format the Table
'   4 columns in rFieldFormats define:
'            Field | Table Part | Property | New Property Value
'   Example: Net Sales | Data | NumberFormat | "$#,##0.00"
    
    Dim c As Range
    Dim sField As String, sFieldRef As String, sTblPart As String
    Dim sProperty As String, sNewValue As String
    On Error Resume Next
    
    For Each c In rFieldFormats.Resize(, 1)
        sField = c(1, 1)
        sTblPart = c(1, 2)
        sProperty = c(1, 3)
        sNewValue = c(1, 4)
        sNewValue = Replace(sNewValue, """", "") 'remove any quotes
        Select Case sTblPart
            Case "Data"
                sFieldRef = tbl.Name & "[" & sField & "]"
            Case "Header"
                sFieldRef = tbl.Name & "[[#Headers]," & _
                    "[" & sField & "]]"
            Case Else 'Default to Both Data and Header
                sFieldRef = tbl.Name & "[[#All]," & _
                    "[" & sField & "]]"
        End Select
        
        With Range(sFieldRef)
            Select Case sProperty
                Case "WrapText"
                    .WrapText = sNewValue
                Case "AutoFit"
                    .EntireColumn.AutoFit
                Case "ColumnWidth"
                    .EntireColumn.ColumnWidth = sNewValue
                Case "Boss Favorite"
                    .WrapText = True
                    With .Font
                        .Name = "Arial Narrow"
                        .Bold = True
                        .Size = 12
                    End With
                Case "Color"
                    .Interior.Color = sNewValue
                Case "Delete"
                    .EntireColumn.Delete
                Case "Font"
                    .Font = sNewValue
                Case "Hidden"
                    .EntireColumn.Hidden = sNewValue
                Case "NumberFormat"
                    .NumberFormat = sNewValue
                Case ""
                    '---No formatting changes
                Case "Property or Method"
                    '---rFieldFormats Header Row
                Case Else
                    MsgBox sProperty & " is not a defined Property " & _
                        "or Method in Function Format_Table"
            End Select
       End With
    Next c
    Set c = Nothing
End Function

It's difficult to get the desired result of breaking a phrase into a certain number of rows using AutoFit and WrapText together.

You'll probably have better luck setting the desired column width of a field like "Contract Modification Number" using ColumnWidth with a tested value, than trying to use AutoFit.

 

[PivotMeThis]

Jerry, you are SO amazing! I am just lovin' this!!!

I added a new property to the table. I inserted Case "Center" and then HorizontalAlignment = xlCenter

So now I can center the column headings as well as wrap the text. I just love the added column in the Drill Down Formatting Table - it gives me so much more flexibility!

You are the best teacher! You have made everything so easy to understand. BIG BIG HUGS!!!

I am having a blast and I am sure all your hard work is going to help lots of people besides me. I am truly grateful for everything you have done.

You deserve 100 gold stars and a big box of cookies!
Thank you thank you thank you thank you SO VERY VERY MUCH!!!

 

[Jerry Sullivan]

Rhonda, You're very welcome. Thank you for your kind feedback.