Tuesday, April 03, 2007

Formating Pivot table items using VBA/Macro

In one of my projects, I need to format the rows in a pivot table so that I can differentiate the different values (e.g. Actual, Budget or Forecast) in a particular field (e.g Type_of_Sales) . I encountered an error when one of the values (e.g. Budget) is not available for the formatting. To resolve the problem, I have to add in a line of code to loop through the values (e.g. Actual, Budget or Forecast) in the field (e.g. Type_of_Sales). If records are found (i.e. pt_item.RecordCount <> 0), then do the formating. If recordcount returns zero, then skip formatting. The code is shown below:

For Each pt_item In .PivotTables("PivotTable1").PivotFields"Type_of_Sales").PivotItems
If pt_item = "Budget" And pt_item.RecordCount <> 0 Then 'do formatting
.PivotTables("PivotTable1").PivotSelect "Budget", xlDataAndLabel, True
Selection.Interior.ColorIndex = 36
Selection.Interior.Pattern = xlSolid
End If
If pt_item = "Fcst" And pt_item.RecordCount <> 0 Then 'do formatting
.PivotTables("PivotTable1").PivotSelect "Fcst", xlDataAndLabel, True
Selection.Interior.ColorIndex = 35
Selection.Interior.Pattern = xlSolid
End If
Next pt_item

2 comments:

Mr. Sober said...

Hiya.. !

I have a similar issue.. Can you have a look at :

http://www.mrexcel.com/forum/showthread.php?mode=hybrid&t=366199&highlight=pivotselect

Thanks a bunch in advance..

JK said...

The syntax doesn't work that way. You can duplicate the code instead and change the copied code from "Nov" to "Dec", etc. Alternatively, record a new macro to find out the syntax.