VBA To Change PIVOT Filter without Errors

[Berlin01]

Scenario:

• I have two PivotTabled in one Excel 2007 worksheet; it is required the PivotTables are side-by-side (horizontally next to each other).
• The left-most PIVOT has a column that holds months; I use the date filter on this column to only show "This Month" or "Last Month" data. As a result, since the column only shows data for one month, it DOES NOT overlap the PIVOT table to the right.
• I can manually change between "This Month" and "Last Month"; however, when I attempt to accomplish the task through a macro, the code calls the ClearAllFilters method before adding the new filter. As a result, when the ClearAllFilters method is called in the MACRO, I encounter the error: "PivotTable report cannot overalap another PivotTable report."

Since, I can accomplish the task of switching from "This Month" to "Next Month" manually without encountering the overlap error, there must be a way to do it using VBA, yes?

any help is appreciated as I have been working for five hours on this one.

 

[DonkeyOte]

If you don't have room to cater for 1 additional item to be visible however briefly I'm not entirely sure you can...

With the manual process item visibility is not processed until such time as "OK" is actioned via the appropriate dialog.
On that basis you can remove one and replace with another without issue given the PT updates only when you click OK.

Should you record the above actions and run the macro it will fail if the Pivots overlap so closely that two items can never be visible simultaneously.
This is simply because the visibility of each item is processed individually and 1+ items must be visible at all times.
(manualupdate makes no difference as I see it)

So you might record yourself successfully toggling visibility of "apple" and "banana" - generating the below code:

Sub Macro1()
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Fruit")
        .PivotItems("Banana").Visible = True
        .PivotItems("Apple").Visible = False
    End With
End Sub

If we then assume selections are reverted (such that Apple is visible) then should you choose to run the above code it would fail (assuming insufficient space to display both Apple & Banana simultaneously)
The fail results because Banana is processed first, then Apple - each time altering the PT output and Banana can not be visible whilst Apple remains visible (space) and Apple can not be hidden until Banana is visible (1+ items visible at all times).

All that being said - that is only my belief - there may be workarounds.