Run-time error 1004 Application-defined or object-defined error - Update Macro used to update a Pivot Table

Question

I have a spreadsheet that I update annually.  In it I have a data table and a pivot table, with a macro that i can run manually to update the pivot table.  This year the macro keeps erroring out.  The code is as follows, I have put the line that errors out in bold:

Any suggestions on how to fix this error?

Sub Actual_Refresh()

'

' Actual_Refresh Macro

'

'

    Application.Goto Reference:="R3C1"

    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

    Columns("F:F").Select

    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Columns("E:E").Select

    Selection.TextToColumns Destination:=Range( _

        "Table_FY20_Actuals.accdb[[#Headers],[Unit]]"), DataType:=xlFixedWidth, _

        FieldInfo:=Array(Array(0, 1), Array(4, 1)), TrailingMinusNumbers:=True

    Columns("F:F").Select

    Selection.Delete Shift:=xlToLeft

    Columns("H:H").Select

    Selection.TextToColumns Destination:=Range( _

        "Table_FY20_Actuals.accdb[[#Headers],[Acct]]"), DataType:=xlFixedWidth, _

        FieldInfo:=Array(Array(0, 1), Array(4, 1)), TrailingMinusNumbers:=True

    Application.Goto Reference:="R3C11"

    ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh

End Sub

[Moved from Excel/ Windows 10/ Microsoft 365 Apps or Office 365 Business]

Answer 1

Try

  ActiveSheet.PivotTables(1).PivotCache.Refresh

Thanks for the TIP!

I found that the error was due to another object on the sheet and not the coding itself.

Answer 2

Try

  ActiveSheet.PivotTables(1).PivotCache.Refresh