Pivot Table - VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone help me with this?
I need a Pivot table to change data according to information in a particular
cell through VB.
For example: if cell M3 is 3500 I want the Pivot table to show the
information for 3500. My poor attempt below

ActiveSheet.PivotTables("PivotTable2").PivotFields("ID").CurrentPage =
"RANGE (M3)"

I can do it using IF's:

If ActiveCell = 3500 Then
ActiveSheet.PivotTables("PivotTable2").PivotFields("ID").CurrentPage =
"3500"
End if

But I would end up with an extremely long code, because there are up to 3500
combinations.
 
ActiveSheet.PivotTables("PivotTable2") _
.PivotFields("ID").CurrentPage = RANGE("M3").Text

Should work if M3 is on the same pate as the pivot table.

if not, you need to specify where it is

ActiveSheet.PivotTables("PivotTable2") _
.PivotFields("ID").CurrentPage = _
worksheets("sheet3").RANGE("M3").Text
 
Many thanks Tom, it worked a treat
John
-----Original Message-----
ActiveSheet.PivotTables("PivotTable2") _
.PivotFields("ID").CurrentPage = RANGE("M3").Text

Should work if M3 is on the same pate as the pivot table.

if not, you need to specify where it is

ActiveSheet.PivotTables("PivotTable2") _
.PivotFields("ID").CurrentPage = _
worksheets("sheet3").RANGE("M3").Text

--
Regards,
Tom Ogilvy




(e-mail address removed)... information in a
particular because there are up to
3500


.
Click to expand...
 
Back
Top