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 replies to "Formating Pivot table items using VBA/Macro"

    • mayhemonger

      Hiya.. !BR/BR/I have a similar issue.. Can you have a look at : BR/BR/A;t=366199amp;highlight=pivotselect/ABR/BR/Thanks a bunch in advance..

    • JK

      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.

Leave a Reply