Hi, In my weekly report I refresh my pivot tables connected to an Access database and then have to manually select the appropriate date ranges which is a hassle since there are too many pivot tables. I'm in the process of writing a macro that can automate this.

I tested my code on the pivot tables and it seems like some of them works while others do not.

here is my code for testing.

Private Sub CommandButton2_Click()
    Application.ScreenUpdating = False
    Dim weekStart, weekEnd As Long
    'Assign values to the date range variables
    weekStart = ActiveWorkbook.Worksheets("Info").Range("B1").Value
    weekEnd = ActiveWorkbook.Worksheets("Info").Range("B2").Value
    'Declare and assign the sheets with pivot tables for updating
    weekSheets = Array("Sheet1")
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim pi As PivotItem
    Set pt = Sheets("Sheet1").PivotTables("PivotTable1")
            pt.ManualUpdate = True
            pt.PivotFields("Date").AutoSort xlManual, "Date"
            For Each pi In pt.PivotFields("Date").PivotItems
                If pi.Value < weekStart Or pi.Value > weekEnd Then
                    pi.Visible = False
                Else
                    pi.Visible = True
                End If
            Next pi
            pt.ManualUpdate = False
            pt.PivotFields("Date").AutoSort xlAscending, "Date"
    Application.ScreenUpdating = True
End Sub
On the tables that work, it refreshes very quickly. But the ones that do not work, the macro causes excel to hang and when I break the macro, I get the error Runtime Error 1004 Unable to set the visible property of the pivotitem class.

What could be the cause of this?
Thanks, all the help is much appreciated