M
Marc T
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
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