Unable to get PivotItems property of the PivotField class - Error 1004

[danpre]

Hey there,

I have good amount of experience in writing code and while recently trying to automate one of the report I am facing problem in Pivot items.

Problem - My codes work fine when all the pivot items in the pivot fields are part of the data for some region but i get error 1004 when in some region some of the pivot items are not available.

Can anyone help me to rewrite the code so that the error is taken care when the specific pivot items are not available. Please see the code for your reference. I get error at the below line of code highlighted in red color even though i am using "on error resume next" this line of code. Appreciate your help around this.


With PT.PivotFields("range")


On Error Resume Next
.PivotItems("0-15").Visible = False
.PivotItems("15-30").Visible = False
.PivotItems("30-45").Visible = False
.PivotItems("45-60").Visible = False
.PivotItems("60-90").Visible = False
.PivotItems("Current Balance").Visible = False
.PivotItems("120-180").Position = 1
.PivotItems("90-120").Position = 1
End With

Below is my complete code starts from here

Dim PTCache As PivotCache
Dim PT As PivotTable 'Create the cache
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
Worksheets.Add 'Create the pivot table
Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, TableDestination:=Range("A3")) 'Specify the fields


On Error Resume Next

With PT.PivotFields("Invoice")
.Orientation = xlRowField
.Position = 1
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With

With PT.PivotFields("Client Name")
.Orientation = xlRowField
.Position = 2
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With

With PT.PivotFields("Client #")
.Orientation = xlRowField
.Position = 3
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With

With PT.PivotFields("range")
.Orientation = xlColumnField
.Position = 1
End With


With PT.PivotFields("range")


On Error Resume Next
.PivotItems("0-15").Visible = False
.PivotItems("15-30").Visible = False
.PivotItems("30-45").Visible = False
.PivotItems("45-60").Visible = False
.PivotItems("60-90").Visible = False
.PivotItems("Current Balance").Visible = False
.PivotItems("120-180").Position = 1
.PivotItems("90-120").Position = 1
End With

PT.AddDataField PT.PivotFields("Total Balance Loc Curr"), "Sum of Total Balance Loc Curr", xlSum

With PT.PivotFields("Sum of Total Balance Loc Curr")
.NumberFormat = "#,##0.00"
End With

 

[danpre]

Hi Team - Can someone provide the solution for the error i am getting as soon as possible?