PDA

View Full Version : Please help - pivot drilldown macro



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

p45cal
09-17-2014, 08:37 AM
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
10-14-2014, 08:41 AM
Yes that is correct! Any suggestions?

karthikumar
06-26-2015, 02:17 PM
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.