VBA, PivotTable, Drill DOwn

Mattador · Jan 21st 2017

Hi,

I have a pivot table that needs regular updating, and I am drilling down into the pivottable data by double clicking the grand total at the bottom.
Instead I would like to assign this as part of a macro.

Think i need to use the pivot table method, but can not find anything complete to play with, I suspect this is really simple.
Assume the pivotable is in sheet one and is called Pivottable1

Code I have tried:

Code

Worksheets("Sheet1").PivotTables("PivotTable1").ShowDetail = True

Please can someone advise me of how to use VBA to automate this with a macro?

Thanks in advance

Matt

TheSilkCode · Jan 21st 2017

Re: VBA, PivotTable, Drill DOwn

Hi Matt- so what exactly are you trying to do? When you click anywhere in a pivot table you see all the data behind it?

-TSC

Mattador · Jan 21st 2017

Re: VBA, PivotTable, Drill DOwn

Yes,

Put I want a macro to do that for multiple pivot tables all at once.

I thought I would keep it simple by using one as an example.

Thanks

TheSilkCode · Jan 21st 2017

Re: VBA, PivotTable, Drill DOwn

Hey Matt-

So this is what i came up with, it aint pretty and assumes that the grand total cell you want to double click on is in the bottom right cell of the pivot table, but it works- see below:

Code

Public Sub ShowAllPTDetail()
Dim WB As Workbook
Dim wsCounter As Long
Dim ptCounter As Long
Dim pt As PivotTable
Dim dataRange As Range
Set WB = ThisWorkbook
For wsCounter = 1 To WB.Sheets.Count
    If WB.Sheets(wsCounter).PivotTables.Count > 0 Then
        With WB.Sheets(wsCounter)
            For ptCounter = 1 To .PivotTables.Count
                Set pt = .PivotTables(ptCounter)
                Set dataRange = Range(pt.TableRange1.Address)
                dataRange(dataRange.Rows.Count, dataRange.Columns.Count).Select
                Selection.ShowDetail = True
            Next
        End With
    End If
Next
End Sub

Display More

Hope this helps,
TheSilkCode

VBA, PivotTable, Drill DOwn

Participate now!

Share

Users Viewing This Thread