Run-time error '1004'

[amrita17170909]

Hi All,

The code is simply to refresh a pivot chart.

I recently introduced an error handler in the code to make it sturdier. I am getting an error as per below:

Run-time error '1004'
Unable to get the PivotTables property of the Worksheet class

Public Sub PBL_SUB_Refresh_Pivotchart()

' Refresh Pivot chart in "Table 1" tab refreshes the data used to calculate the pivot chart
ActiveWorkbook.RefreshAll

With ActiveSheet.PivotTables("PivotTable6").PivotFields("BRN Description")
        .PivotItems("0").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    
ErrHandler:
Debug.Print Err.Number, Err.Description, Err.Source
MsgBox "Error " & Err.Number & ": " & Err.Description & Chr(13) & "From: " & Err.Source & Chr(13) & "Function : " & "PBL_SUB_Refresh_Pivotchart"

Resume Next

End Sub

Can someone advise what am I doing wrong ?

Amrita

 

[GWteB]

The only cause I can think of is the activesheet is NOT the sheet where the requested pivottable actual is located.

 

[amrita17170909]

Yep included the activesheet and it works now

My errorhandler still comes up with a run-time error .

 

[DanteAmor]

Having an error handler does not sturdier, I consider it weaker. It is better to try to validate the possible error.

The code would look like this:

Public Sub PBL_SUB_Refresh_Pivotchart()
  ' Refresh Pivot chart in "Table 1" tab refreshes the data used to calculate the pivot chart
  ActiveWorkbook.RefreshAll

  On Error GoTo ErrHandler

  With ActiveSheet.PivotTables("PivotTable6").PivotFields("BRN Description")
    .PivotItems("0").Visible = False
    .PivotItems("(blank)").Visible = False
  End With

  Exit Sub
  
ErrHandler:
  Debug.Print Err.Number, Err.Description, Err.Source
  MsgBox "Error " & Err.Number & ": " & Err.Description & Chr(13) & "From: " & Err.Source & Chr(13) & "Function : " & "PBL_SUB_Refresh_Pivotchart"
End Sub

_________________________________________

Verifying if the data exists, it would look like this:

Public Sub PBL_SUB_Refresh_Pivotchart()
  Dim f As Range
  ' Refresh Pivot chart in "Table 1" tab refreshes the data used to calculate the pivot chart
  ActiveWorkbook.RefreshAll
  With ActiveSheet.PivotTables("PivotTable6").PivotFields("BRN Description")
    Set f = Range(.DataRange.Address).Find("0", , xlValues, xlWhole)
    If Not f Is Nothing Then
      .PivotItems("0").Visible = False
    End If
    Set f = Range(.DataRange.Address).Find("(blank)", , xlValues, xlWhole)
    If Not f Is Nothing Then
      .PivotItems("(blank)").Visible = False
    End If
  End With
End Sub