pivot table refresh - error 1004 - reference is not valid

**jrussell** · 11-13-2008, 05:09 PM

I have a macro that refreshes 4 pivot tables that refer to data on another sheet in the same workbook. I want the sheet containing the pivot tables to be protected but still have the pivot tables be able to update. It works fine on my copy of Excel 2007, but other people running Excel 2003 can't get it to work. They get error 1004 - "reference is not valid".

Sub UpdateAllPivotTables()
    Dim pt As PivotTable
    With ActiveSheet
        .Protect Password:="mypassword", UserInterfaceOnly:=True
        For Each pt In .PivotTables
            pt.RefreshTable
        Next pt
    End With
End Sub

The debugger highlights this: