Pivot Table Drilldown - different deleted columns, formatting depending on pivot table clicked.

L

Legacy 422070

Guest
Hi, this question is an expansion on a previous question which had an answer to drilldown a pivot table to hide or delete particular columns and apply formatting:
https://www.mrexcel.com/forum/excel...olumns-range-instead-table-7.html#post5003258

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

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Hi @lofiloop,

Below is some code to try, along with some setup steps.

Paste this code into the ThisWorkbook Module...

Code: 
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
 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 ResetPublicStrings
 With Target.PivotCell
   If .PivotCellType = xlPivotCellValue And _
         .PivotTable.PivotCache.SourceType = xlDatabase Then
      sSourceDataR1C1 = .PivotTable.SourceData
      sPivotName = .PivotTable.Parent.Name & "!" & .PivotTable.Name
   End If
 End With
 Exit Sub

ResetPublicStrings:
 sSourceDataR1C1 = vbNullString
 sPivotName = vbNullString
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 Dim tblNew As ListObject
 
 On Error Resume Next
 Set tblNew = Cells(1).ListObject
 On Error GoTo 0

 If tblNew Is Nothing Then Exit Sub

 Call Format_PT_Detail(tblNew)
End Sub

Paste this code into a Standard Code Module...
The names in red need to be modified to match the sheet names, table names, and pivottable names in your workbook.
Code: 
Public sSourceDataR1C1 As String
Public sPivotName As String


Public Sub 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 Sub
 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)
   
   tblNew.Unlist   'Optional: Converts Table to Standard Range

 End With
 
 '--reset public vars
 sSourceDataR1C1 = vbNullString
 sPivotName = vbNullString
End Sub

Private Sub Format_Table(tbl As ListObject)
'---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 rCell As Range, rFieldFormats As Range
 Dim sField As String, sFieldRef As String, sTblPart As String
 Dim sProperty As String, sNewValue As String

 On Error Resume Next
 
 '--edit to map each PivotName to a specific drilldown format range
 With Sheets("[COLOR="#FF0000"]DrillDownFormats[/COLOR]")
   Select Case sPivotName
      Case "[COLOR="#FF0000"]PivotSheet1!PivotTable3[/COLOR]"
         Set rFieldFormats = .ListObjects("[COLOR="#FF0000"]FormatTable1[/COLOR]").DataBodyRange
      Case "[COLOR="#FF0000"]PivotSheet2!PivotTable8[/COLOR]"
         Set rFieldFormats = .ListObjects("[COLOR="#FF0000"]FormatTable2[/COLOR]").DataBodyRange
      Case Else
      '--optional: default drill down format of pivots not listed above
         Set rFieldFormats = .ListObjects("[COLOR="#FF0000"]FormatTable1[/COLOR]").DataBodyRange
   End Select
 End With
        
 For Each rCell In rFieldFormats.Resize(, 1)
   sField = rCell(1, 1)
   sTblPart = rCell(1, 2)
   sProperty = rCell(1, 3)
   sNewValue = rCell(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 rCell
End Sub

Add a sheet in your workbook and name it "DrillDownFormats". This is the sheet where you will put tables that provide the additional formatting for each specified PivotTable's drill down data. I think it's better to use Excel tables for this instead of standard ranges in case they get moved on the sheet. Name the tables "FormatTable1" and "FormatTable2", or else modify the VBA code in Sub Format_Table to match the names you use.

Here's an example based on the fields in the old thread you linked.

Excel 2016
ABCD
1FormatTable1
2Data Source HeaderTable PartProperty or MethodValue
3Letting DateBothHiddenTRUE
4DesignerData
5OfficeHiddenTRUE
6ContractorBothHiddenTRUE
7Project Awarded AmountDataNumberFormat"$#,##0.00"
8Project Authorized AmountDelete
9Awarded QuantityDataNumberFormat"0.000"
10Awarded QuantityDataColor10092543
11From QuantityBothBoss Favorite
12Contract Modification NumberBothColumnWidth12
13Contract Modification NumberHeaderWrapTextTRUE
14
15FormatTable2
16Data Source HeaderTable PartProperty or MethodValue
17Project Awarded AmountDataColumnWidth20
18Project Authorized AmountDelete
19Awarded QuantityDataNumberFormat"0.0"
DrillDownFormats

Lastly, modify the VBA code in Sub Format_Table so that your PivotTables are mapped using the pattern "Sheetname!PivotTableName" to match the actual names in your workbook.
 
Last edited:
Upvote 0
Jerry Sullivan said:
Hi @lofiloop,

Below is some code to try, along with some setup steps.

Paste this code into the ThisWorkbook Module...

Code: 
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
 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 ResetPublicStrings
 With Target.PivotCell
   If .PivotCellType = xlPivotCellValue And _
         .PivotTable.PivotCache.SourceType = xlDatabase Then
      sSourceDataR1C1 = .PivotTable.SourceData
      sPivotName = .PivotTable.Parent.Name & "!" & .PivotTable.Name
   End If
 End With
 Exit Sub

ResetPublicStrings:
 sSourceDataR1C1 = vbNullString
 sPivotName = vbNullString
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 Dim tblNew As ListObject
 
 On Error Resume Next
 Set tblNew = Cells(1).ListObject
 On Error GoTo 0

 If tblNew Is Nothing Then Exit Sub

 Call Format_PT_Detail(tblNew)
End Sub

Paste this code into a Standard Code Module...
The names in red need to be modified to match the sheet names, table names, and pivottable names in your workbook.
Code: 
Public sSourceDataR1C1 As String
Public sPivotName As String


Public Sub 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 Sub
 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)
   
   tblNew.Unlist   'Optional: Converts Table to Standard Range

 End With
 
 '--reset public vars
 sSourceDataR1C1 = vbNullString
 sPivotName = vbNullString
End Sub

Private Sub Format_Table(tbl As ListObject)
'---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 rCell As Range, rFieldFormats As Range
 Dim sField As String, sFieldRef As String, sTblPart As String
 Dim sProperty As String, sNewValue As String

 On Error Resume Next
 
 '--edit to map each PivotName to a specific drilldown format range
 With Sheets("[COLOR=#FF0000]DrillDownFormats[/COLOR]")
   Select Case sPivotName
      Case "[COLOR=#FF0000]PivotSheet1!PivotTable3[/COLOR]"
         Set rFieldFormats = .ListObjects("[COLOR=#FF0000]FormatTable1[/COLOR]").DataBodyRange
      Case "[COLOR=#FF0000]PivotSheet2!PivotTable8[/COLOR]"
         Set rFieldFormats = .ListObjects("[COLOR=#FF0000]FormatTable2[/COLOR]").DataBodyRange
      Case Else
      '--optional: default drill down format of pivots not listed above
         Set rFieldFormats = .ListObjects("[COLOR=#FF0000]FormatTable1[/COLOR]").DataBodyRange
   End Select
 End With
        
 For Each rCell In rFieldFormats.Resize(, 1)
   sField = rCell(1, 1)
   sTblPart = rCell(1, 2)
   sProperty = rCell(1, 3)
   sNewValue = rCell(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 rCell
End Sub

Add a sheet in your workbook and name it "DrillDownFormats". This is the sheet where you will put tables that provide the additional formatting for each specified PivotTable's drill down data. I think it's better to use Excel tables for this instead of standard ranges in case they get moved on the sheet. Name the tables "FormatTable1" and "FormatTable2", or else modify the VBA code in Sub Format_Table to match the names you use.

Here's an example based on the fields in the old thread you linked.
Excel 2016
ABCD
FormatTable1
FormatTable2

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #00FFFF"]Data Source Header[/TD]
[TD="bgcolor: #00FFFF"]Table Part[/TD]
[TD="bgcolor: #00FFFF"]Property or Method[/TD]
[TD="bgcolor: #00FFFF"]Value[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Letting Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Both[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Hidden[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]TRUE[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Designer[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Data[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Office[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Hidden[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]TRUE[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Contractor[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Both[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Hidden[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]TRUE[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Project Awarded Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Data[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]NumberFormat[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]"$#,##0.00"[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Project Authorized Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Delete[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Awarded Quantity[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Data[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]NumberFormat[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]"0.000"[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Awarded Quantity[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Data[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Color[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]10092543[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]From Quantity[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Both[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Boss Favorite[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Contract Modification Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Both[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]ColumnWidth[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]12[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Contract Modification Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Header[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]WrapText[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]TRUE[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #00FFFF"]Data Source Header[/TD]
[TD="bgcolor: #00FFFF"]Table Part[/TD]
[TD="bgcolor: #00FFFF"]Property or Method[/TD]
[TD="bgcolor: #00FFFF"]Value[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Project Awarded Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Data[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]ColumnWidth[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]20[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Project Authorized Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Delete[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Awarded Quantity[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Data[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]NumberFormat[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]"0.0"[/TD]

</tbody>
DrillDownFormats​


Lastly, modify the VBA code in Sub Format_Table so that your PivotTables are mapped using the pattern "Sheetname!PivotTableName" to match the actual names in your workbook.
Click to expand...
Thank you so much for this Jerry, I will give it a try when I'm in the office on Wednesday and let you know how it goes.
 
Upvote 0
@jerrysullivan

Thank you so much for this Jerry, after a little playing around with the pivottable names and organising the format tables so that they run alongside each other as they have many rows, this method is working perfectly. You've made my month!
 
Last edited by a moderator:
Upvote 0
Hello Jerry,


I have an issue similar except that I want the drill down sheet to maintain the same format as my Base sheet.
I have a field called CPU Average in my Table1 which shows values in % and I want the drilldown sheet to show in % .Example 99% not 0.99


I have tried all the methods reading from other blogs as "Format as Table->New style->keep default" and tried the Cell Styles->Format it to %.Tried to select and format the cell values to %.No matter what I do my pivottable shows up perfect but the drill down sheet formats the field to General.


I finally created an account just to get an answer for this issue.can VB Fix this?,Please help me in writing a code.I see you are a VB Master, please help me as well.


Table1- (Base table name)
Pivottable1 (Pivot table name)
Book1(work book name)

Many Thanks!
 
Upvote 0
Hello Jerry,

Firstly thank you for replying to my question.

I have tried the codes you have provided earlier.
When I save these and try to run, I dont see any Macros available to run in my workbook.Sorry I am new to VBA please help me if I need to rename something in the codes to match the names of my sheets.

New.xlsm is the name of my workbook




If my screenshots/images dont show up:Please see the look alike VBA screenshot page I made.


(-)VBA Project (New.xlsm)->Inserted 3rd code into this module.
(-)Microsoft Excel Objects
[X]Sheet1(Pivot table) ->Inserted 1st code into the module.
[X]Sheet2(Table1)
[X]This workbook module->Inserted 2nd code into the module.

Save->

Went to the Pivot table sheet->Developer->Macros->No Macro names listed there to click run.
 
Upvote 0
Hi Jerry!

This is great (just like you!!!) and I'm looking forward to trying it out. Always good to see you on the board!

Thanks for all you do and have a great day!
 
Upvote 0
Hi Rhonda,

It's so nice to hear from you. It's been a couple of years since we shared a thread. :)

You can see from the links above that your original thread about customizing the drilldown is still a valuable reference for variations on that theme.


PivotMeThis said:
Hi Jerry!

This is great (just like you!!!) and I'm looking forward to trying it out. Always good to see you on the board!

Thanks for all you do and have a great day!
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Timeline on Pivot Table not working
Replies
1
Views
205
Sergius
S
J
  • Question Question
Conditional formatting of single column of pivot table
Replies
8
Views
238
Peter_SSs
Peter_SSs
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
183
Jette3173
J
T
  • Question Question
Pivot table: How to calculate new columns based on the pivot table itself?
Replies
4
Views
216
TheWes
T
G
  • Question Question
Conditional Formatting applied to the whole column in Pivot Table?
Replies
0
Views
289
GoodInSheets
G

Forum statistics

Threads
1,223,927
Messages
6,175,443
Members
452,641
Latest member
Arcaila

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top