Using macro to switch 'page' in a pivot table?
 

Hi All,

I have to following simple macro which updates two pivot tables with data
and only shows data relating to a particular project ID 'a':

Sub Macro1()

Sheets("CONTROLS").Select
a = Range("B4").Value

Sheets("Labour Costs").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable2").PivotFields ("Project
No").CurrentPage = a


Sheets("Non-Labour Costs").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Project
ID").CurrentPage = a

Sheets("CONTROLS").Select

End Sub


In some cases the value 'a' doesn't exist in the data range that the pivot
tables are referring to (no costs on the project etc). Is there any way to
force the pivot tables to show as empty as opposed to throwing an error or a
dummy number with another project ID's data?

Many thanks!
Marc

Using macro to switch 'page' in a pivot table?
 

I've managed to find this bit of code, but am stumped at how to adapt it for
the problem:

Dim Pf As PivotField
Set Pf = ActiveSheet.PivotTables("MyPivotTable").PivotField s("MyField")
If Pf is Nothing Then
'Not There
Else
'Is There
End If


The source data for the pivot table contains multiple project ID's, so I'm
needing to have the if statement check if the Project ID exists in the data
as opposed to checking if the field itself exists.

Any help would be much appreciated!
Marc

"Marc T" wrote:

Hi All,

I have to following simple macro which updates two pivot tables with data
and only shows data relating to a particular project ID 'a':

Sub Macro1()

Sheets("CONTROLS").Select
a = Range("B4").Value

Sheets("Labour Costs").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable2").PivotFields ("Project
No").CurrentPage = a


Sheets("Non-Labour Costs").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Project
ID").CurrentPage = a

Sheets("CONTROLS").Select

End Sub


In some cases the value 'a' doesn't exist in the data range that the pivot
tables are referring to (no costs on the project etc). Is there any way to
force the pivot tables to show as empty as opposed to throwing an error or a
dummy number with another project ID's data?

Many thanks!
Marc