Pivot Table - VBA
 

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.

Pivot Table - VBA
 

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



"JohnUK" wrote in message
...
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.

Pivot Table - VBA
 

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



"JohnUK" wrote in
message
news:8E93CCC2-A05F-4EF8-84F8-
...
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.


.