PDA

View Full Version : macro for pivot



neocuproine
09-19-2011, 01:55 PM
Hello,

I am totally unexperienced in VBA but I have a Pivot and would like to create a macro (Macro1) to choose the "FRUIT" field of my pivot based on the cell D2 of my spreadsheet. I don't know how to do it as I have the following code:

Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("data").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("FRUIT").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("FRUIT").CurrentPage = _
"PEARS"
End Sub


I want to replace "PEARS" by the content of cell D2 (which can be PEARS or APPLES or BANANAS or CHERRIES or MELONS...

Any idea anyone?

Thanks for help!

Bob Phillips
09-19-2011, 02:05 PM
Sub Macro1()
'
' Macro1 Macro
'
'
With Sheets("data")
.PivotTables("PivotTable1").PivotFields("FRUIT").ClearAllFilters
.PivotTables("PivotTable1").PivotFields("FRUIT").CurrentPage = .Range("D2").Value
End With
End Sub