Please help - pivot drilldown macro
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? :dunno
Thanks so much in advance! :)
Can we add multiple PIVOT TABLEs in single sheet?
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
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.