PDA

View Full Version : Changing the value of a pivot item



joshcas
01-13-2014, 10:33 AM
Hi ,

This morning I'm having a difficult challenge , I'm trying to change the Pivotitems with a "Blank" value to "Revenue" for a particular PivotField , I thought it might be really simple but it's not , can you guys point me to the correct direction ? :)


Here is the code that I'm using , doesn't seem to be updating the pivot tables


Sub Clear_Blank_PivotItems()


On Error Resume Next


With ThisWorkbook


For Each WS In .Worksheets
For Each PT In WS.PivotTables
' PT.PivotCache.MissingItemsLimit = xlMissingItemsNone
For Each PI In PT.PivotFields("SNP Quarterly Level 4").PivotItems
If PI.Name = ("") Then
'MsgBox ("true")
PI.Value = "Revenues "
End If
Next PI
PT.PivotCache.Refresh
Next P
Next WS

End With




End Sub

fredlo2010
01-13-2014, 11:36 AM
Hi,

If you want to show a value in the fields Instead of the "" default. Try this:


Sub Try()

Dim ws As Worksheet
Dim PT As PivotTable


For Each ws In ThisWorkbook.Worksheets
On Error Resume Next
Set PT = ws.PivotTables(1)
With PT
PT.NullString = "Revenue"
PT.PivotCache.Refresh
End With
On Error GoTo 0
Next
End Sub

Thanks

joshcas
01-13-2014, 12:02 PM
Hi fredlo2010 , thank you so much for your time to answer , I really appreciate it


I've tried your code and it didn't work because is still showing "blank" in that particular field

fredlo2010
01-13-2014, 12:24 PM
Can you attach small sample of your workbook with dummy data. With a before and after? Thanks