Scott
I don't know the reason why you get this error. Next procedure may help you to determine which pivottables is causing troubles so you can focus on that specific PowerPivotTable. The macro can also be handy to disable the refresh of pivottables on file opening.
The second macro is just listing the pivottables in your workbook and on which worksheet they reside together with their source(table). For this macro you need to add a worksheets with label name 'Docs'.
What operating system and version of MS Excel (32/64bit) / PowerPivot are you using?
Sub RefreshAllPivotTables()
' Refresh all Pivottables in workbook
' Disable pivottable refresh on open of workbook
Dim w As Worksheet, p As PivotTable
For Each w In ThisWorkbook.Worksheets
For Each p In w.PivotTables
'p.PivotCache.RefreshOnFileOpen = False
MsgBox "PivotTable " & w.Name & "." & p.Name & " is going to be refreshed", vbOKOnly
p.PivotCache.Refresh
Next
Next
End Sub
Sub PrintPivotTables()
' Disable pivottable refresh on open
On Error Resume Next
Dim w1 As Worksheet
Dim w2 As Worksheet
Dim p As PivotTable
Dim i As Long
Set w2 = ActiveWorkbook.Worksheets("Docs")
i = 5
For Each w1 In ThisWorkbook.Worksheets
For Each p In w1.PivotTables
i = i + 1
w2.Cells(i, 2) = w1.Name
w2.Cells(i, 3) = p.Name
w2.Cells(i, 4) = Mid(p.RowRange.PivotField.Name, 2, InStr(p.RowRange.PivotField.Name, "]") - 2)
p.PivotCache.RefreshOnFileOpen = False
Next
Next
End Sub
Eddy Nijs
