Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to select dates in the page field of a pivot table | Excel Programming | |||
Pivot Table Page Area selections in a macro | Excel Programming | |||
Pivot table page field switch to (all) if my criteria is not avail | Excel Discussion (Misc queries) | |||
Changing a pivot table page or data set by using a macro | Excel Programming | |||
VBA Code for a pivot table to open database and to reference table current page | Excel Programming |