scott511
09-15-2014, 07:11 AM
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! :)
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! :)