Macro that works on PC doesn't work on Mac.

**elrader** · 04-09-2012, 01:04 PM

Below is the macro code. The code automatically updates pivot filters across the sheets in a workbook. It works perfectly fine in Excel 2007 on a PC. It does not work at all on Excel 2011 on a Mac.

A few more details.

- There are hidden sheets that have pivot tables
- The un-hidden sheets have pivot tables as well and they have filters that when changed, change data on the hidden sheets, which then produces new graphs on the un-hidden sheets.
- The pivot table filters on the un-hidden sheets on the mac just look like regular cells - they don't have the drop down that the filters on the PC versions have. The macro code is still there on the Mac files, but it's almost like the macro completely breaks the pivot table filter on the mac.

Any help would be very much appreciated. Thanks!!

Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean

On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
                pt.ManualUpdate = True
                Set pf = pt.PivotFields(pfMain.Name)
                        bMI = pfMain.EnableMultiplePageItems
                        With pf
                            .ClearAllFilters
                            Select Case bMI
                                Case False
                                    .CurrentPage = pfMain.CurrentPage.Value
                                Case True
                                    .CurrentPage = "(All)"
                                    For Each pi In pfMain.PivotItems
                                        .PivotItems(pi.Name).Visible = pi.Visible
                                    Next pi
                                    .EnableMultiplePageItems = bMI
                            End Select
                        End With
                        bMI = False
                
                Set pf = Nothing
                pt.ManualUpdate = False
            End If
        Next pt
    Next ws
Next pfMain
    
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

**Richard Buttrey** · 04-09-2012, 01:09 PM

On which line does it break down?

**elrader** · 04-09-2012, 01:33 PM

It actually does not generate any errors in executing the code. A few more details I should have mentioned earlier.

There are hidden sheets that have pivot tables
The un-hidden sheets have pivot tables as well and they have filters that when changed, change data on the hidden sheets, which then produces new graphs on the un-hidden sheets.
The pivot table filters on the un-hidden sheets on the mac just look like regular cells - they don't have the drop down that the filters on the PC versions have. The macro code is still there on the Mac files, but it's almost like the macro completely breaks the pivot table filter on the mac.

**Richard Buttrey** · 04-09-2012, 01:40 PM

Hi,

In that case you need first to establish whether they are indeed pivot tables. Can you manipulate them manually? If not then the VBA macro will obviously not understand any PT reference and you'll need to determine what is causing the MAC to lose the Pivot Tables. Are you certain that you are opening exactly the same file on the MAC as on the PC or someone else's copy of it?

**elrader** · 04-09-2012, 03:52 PM

@firefly - took that line out of the macro but to no avail. There were never any run time errors before - it seems like the macro makes a pivot table not think its a pivot table on the unhidden sheets where the only pivot table element is a filter.

@Richard, it is definitely the same file. I can go in and manipulate the pivots manually. I can even click into the "broken" filter (where the filter does not show a drop down) and click on the pivot table builder and it recognizes it as a pivot table as it gives me all the regular pivot table options.