PivotItems("(blank)").Visible = False gives error 400 on one computer but runs without error on another

I have a macro which helps users set and clear filters on a PivotTable. The macro has been working fine with my whole office for months. Then for seemingly no reason, one persons computer started getting error 400 on the line: MySheet.MyPivotTable.MyPivotField.PivotItems("(blank)").Visible = False

For reference, the actual line is pf_New.PivotItems("(blank)").Visible = False where pf_New is a PivotField Object defined as Me.PivotTables("Usage").PivotFields(Sheet1.[UsedOn6].Column - 6 + new_machine) where new_machine is an integer argument of the sub.

There are certainly blank items in the table's source data for that field. The line works on my computer and I can confirm that it is removing blank items from the table. But when I move to my co-workers computer and run the line, it gives error 400.

Does anyone have any ideas behind the cause, or suggestions on how to avoid the error? Is there an alternate method to filtering blank rows from this pivotfield?

Solution

After doing more investigating on the PC causing problems, I found that the cause of the error was a combination of other settings and code that failed to set up the table properly for filtering.

In the pivot table options, "Save source data with file" was unchecked and "Refresh data when opening the file" was unchecked. This was to cut down on file size and reduce the delay when opening the file.

But having those two options off means that the pivot table has no data when opening the file. Interacting with the table in Excel will display the error "The PivotTable report was saved without the underlying data. Use the Refresh Data command to update the report" but when interacting with it in VBA you instead get the error "Out of Memory" or "400".

To avoid this issue, use ThisWorkbook.RefreshAll or YourPivotTable.RefreshTable before attempting to apply or change any filters.

In my file, the reason why it happened to only one person and not the rest of my co-workers was because my Workbook_Open event does have the line ThisWorkbook.RefreshAll after a bunch of other processes. For an unknown reason that I am still investigating, this one person's PC was skipping that line.