运行VBA刷新数据透视表时触发1004对象定义错误求助

Let’s break down why you’re hitting that 1004 object-defined error and get your pivot table and its linked chart automatically showing the last 30 days of data every day.

Common Causes of the 1004 Error #

Your code looks mostly right, but here are the top culprits for that error:

• Duplicate filter already exists: If you’ve run this macro before, the xlDateBetween filter on "DateAdded" is already applied. Trying to add it again throws an error.
• Incorrect pivot field name: Double-check that "DateAdded" is the exact name of the field in your pivot table (not just the source data—pivot fields sometimes get renamed).
• Non-date data type: If the "DateAdded" field in your pivot table isn’t recognized as a date (e.g., stored as text), the date filter won’t work.

Fixed Macro Code #

Here’s an updated version of your code that addresses these issues. It first clears any existing filters on the "DateAdded" field before applying the 30-day range:

' Refresh pivot table filter to show last 30 days
Sub Refresh30DayPivot()
    Dim DateToday As Date
    DateToday = Date
    Dim PvtTbl As PivotTable
    Dim PvtField As PivotField
    
    ' Set reference to your pivot table
    Set PvtTbl = Worksheets("Sheet1").PivotTables("Pivot1")
    Set PvtField = PvtTbl.PivotFields("DateAdded")
    
    ' Turn off screen updating to speed things up and prevent flicker
    Application.ScreenUpdating = False
    
    On Error Resume Next ' Ignore error if no filter exists to clear
    ' Clear existing filters on the DateAdded field
    PvtField.ClearAllFilters
    On Error GoTo 0 ' Reset error handling
    
    ' Apply the 30-day date range filter
    PvtField.PivotFilters.Add2 _
        Type:=xlDateBetween, _
        Value1:=DateToday - 30, _
        Value2:=DateToday
    
    ' Refresh the pivot table to pull latest data first (critical for auto-update!)
    PvtTbl.RefreshTable
    
    ' Turn screen updating back on
    Application.ScreenUpdating = True
End Sub

Setting Up Daily Auto-Update #

To make this run automatically every day:

1. Use Workbook Open Event: If you want the pivot to refresh when you open the workbook, add this to the ThisWorkbook module:

   Private Sub Workbook_Open()
       Refresh30DayPivot
   End Sub
   

2. Scheduled Refresh (for open workbooks): If you need it to refresh throughout the day while the workbook is open, add this to a standard module to set a daily timer:

   Sub SetDailyRefresh()
       ' Schedule refresh for 9 AM tomorrow (adjust time as needed)
       Application.OnTime TimeValue("09:00:00"), "Refresh30DayPivot", , True
   End Sub
   

   Run SetDailyRefresh once, and it will trigger the pivot refresh every day at your chosen time.

Ensuring the Linked Chart Updates #

Good news—when you refresh the pivot table, its linked chart should update automatically. If it doesn’t, add this line to the end of the Refresh30DayPivot sub to force a chart refresh:

' Replace "Chart 1" with your chart's name
Worksheets("Sheet1").ChartObjects("Chart 1").Chart.Refresh

Quick Checks to Avoid Future Errors #

• Verify that "DateAdded" in the pivot table matches the exact field name (check the pivot table field list).
• Confirm the source data’s "DateAdded" column is formatted as a date (not text). You can fix this in the source sheet by selecting the column, going to Home > Number Format > Short Date.

内容的提问来源于stack exchange，提问作者UserX