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!

 

[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!

Also, for example the width of columns are different when Drill Down is summoned. Few columns differs by 20 pixels which is more than enough for the value to be shown as ###########. Is this related also to the differently showed up formats?
We have checked the settings / pc's regional settings and kind of lost here.

another option: Is it possible to inject a rule into the code for formatting purposes after the drill down is opened, for example something like:
.ListColumns(12).Range.NumberFormat = "mm/dd/yyyy hh:mm am/pm"
I have tried to put this line in: Public Function Format_PT_Detail(tblNew As ListObject) after/in the "if" but this does not help.

 

[Jerry Sullivan]

I don't recommend hard coding in ListColumn(12) to have a specific format. Anytime you add or remove a column from your datasource then you would need to modify the VBA code.
Try this modified version of Format_PT_Detailcode which will find the field name you specify (modify "MyDateTimeField" to your actual field name).

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
            If .ListColumns(lCol).Name = "MyDateTimeField" Then
               .ListColumns(lCol).Range.NumberFormat = _
                  "mm/dd/yyyy hh:mm am/pm"
            Else
               .ListColumns(lCol).Range.NumberFormat = _
                   cSourceTopLeft(2, lCol).NumberFormat
            End If
        Next lCol
        .Range.EntireColumn.AutoFit
    End With
    sSourceDataR1C1 = vbNullString
    cSourceTopLeft = Nothing
End Function

 

[rolandas199]

Hello, Jerry,
problems seems to remain the same - even though the formatting is forced through VBA, on some computers in the network everything seems ok, and on others date is shown as mm/dd yyyy.
Would you happen to know what is the root cause of this issue? Thank you!

 

[Jerry Sullivan]

Try comparing the custom number format for that range for users' that display time, and one's that don't.

If that field displays in format "mm/dd/yyyy hh:mm am/pm" on one computer and that workbook is saved and given to a user with a problem computer, does that same workbook display as "mm/dd/yyyy" when they open the file (without doing their own drilldown)?

 

[Legacy 422070]

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.

I need to get this working on two pivot tables on the same sheet (or could be different sheets if necessary). I need each to use a different table to decide which columns to delete and the formats required. I can get one pivot table working using the method at the beginning of this post but can't get two working independently. Please help!!!!

 

[Legacy 422070]

I need to get this working on two pivot tables on the same sheet (or could be different sheets if necessary). I need each to use a different table to decide which columns to delete and the formats required. I can get one pivot table working using the method at the beginning of this post but can't get two working independently. Please help!!!!

Bump! Please help!

 

[Jerry Sullivan]

Hi @lofiloop,

I'd suggest you start a new thread for your question.

If you a post here with a link to your new thread, I'll try to help.

 

[Legacy 422070]

Hi @lofiloop,

I'd suggest you start a new thread for your question.

If you a post here with a link to your new thread, I'll try to help.

HI @jerry-Sullivan, opened as new thread:

https://www.mrexcel.com/forum/excel...epending-pivot-table-clicked.html#post5003430

 

[Legacy 422070]

HI @jerry-Sullivan, opened as new thread:

https://www.mrexcel.com/forum/excel...epending-pivot-table-clicked.html#post5003430

Sorry should have put @jerrysullivan instead of @jerry-sullivan

 

[surtaurus]

Hi Jerry,
This code works perfect for English excel. But if same excel file opened in German version does'nt display as required. Can you please help with this problem? I am using Excel 2016.

Thanks & Regards,
Sur