Please help - pivot drilldown macro

[scott511]

Hey! I'm new to creating macros and I'm stuck on trying to create a pivot after I've drilled down from an original pivot. The problem I'm having is every time you double click a cell in the pivot to drilldown it creates a new sheet or table (such as sheet1 then sheet2 the next time and so on) so the macro will stop running because it references the wrong sheet or table. Here is what I have below:

Range("E8").Select
Selection.ShowDetail = True
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
:="Sheet5!R3C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion14
Sheets("Sheet5").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Payer")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("new charge"), "Sum of new charge", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of new charge")
.Caption = "Count of new charge"
.Function = xlCount
End With
Range("B7").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Payer").AutoSort _
xlDescending, "Count of new charge", ActiveSheet.PivotTables("PivotTable1"). _
PivotColumnAxis.PivotLines(1), 1
Rows("4:4").Select
ActiveWindow.FreezePanes = True
Range("C2").Select
End Sub


The red font is where it gets held up. Also, what do I need to use for it to work on any cell I double click? Or what can be used to trigger the macro when I double click any cell?

Thanks so much in advance!

[p45cal]

So you want to drill down on a cell in an existing pivot table which creates a fresh table on a new sheet. You then want to use this new table/sheet as source data for a new pivot?

[scott511]

Yes that is correct! Any suggestions?

[karthikumar]

I have one doubt regarding PIVOT TABLE.

Is it possible to add multiple pivot tables in single sheet or sheet which has already pivot table?

I have a code for creating pivot in new sheet. However, i couldn't add more pivot table in single sheet.?

Here is my code

Sub summary_overall3()

    Dim wksSource As Worksheet
    Dim wksDest As Worksheet
    Dim rngSource As Range
    Dim rngDest As Range
    Dim LastRow As Long
    Dim LastCol As Long
    Dim destin As Range
    Sheets("elog_status").Cells.Clear
    'Sheets("Pivot").Visible = True
    Set wksSource = Worksheets("Reconsile")
    Set destin = "R1C" & Range("A1").CurrentRegion.Columns.Count + 3
    Set wksDest = Worksheets("Overall summary") '"R1C" & Range("A1").CurrentRegion.Columns.Count + 3
    
    With wksSource
        LastRow = Worksheets("Reconsile").Range("A1").End(xlDown).Row
        LastCol = Worksheets("Reconsile").Range("A1").End(xlToRight).Column
        Set rngSource = .Range("A1", .Cells(LastRow, LastCol))
       ' Set rngSource = Sheets("Reconsile").Range("A1:AZ")
    End With
    
    Set rngDest = wksDest.Range("A3")
    
ActiveSheet.PivotTableWizard _
        SourceType:=xlDatabase, _
        SourceData:=rngSource, _
        TableDestination:=destin, _
        TableName:="Reconsile"
        
    With wksDest.destin.PivotTables("Reconsile")
        .PivotFields("Owner").Orientation = xlRowField
        .PivotFields("country").Orientation = xlDataField
        '.PivotFields("ACCOUNT_NO").Orientation = xlRowField
        .PivotFields("countyr").Orientation = xlColumnField
        '.PivotFields("AMOUNT_EUR").Orientation = xlDataField
    End With
ActiveSheet.PivotTables("Reconsile").TableStyle2 = "Reconsile"
End Sub

Kindly suggest me to solve this.

Thanks in advance.