Excel VBA Macro: Changing and filtering pivot works for me, not for my colleagues

[hoksie]

Hi all,

Thanks in advance for your help. I found a lot of nice tips and tricks on this forum!

Situation:
I have created a dashboard based on a pivot table. I use different macros to filter data and change the dashboard. I have made different rectangles where I assign macros to. I have two types of macros. First kind of macro uses the autofilter, the other kind of macro changes the pivot table. I change the pivot table by filtering the data and the a macro takes away column headers and place new column headers instead. I recorded this macro.

My problem:
It works fine for me, but not for my two colleagues. They get the following message (translated out of Dutch): ‘Error 1004 during execution by application or object-defined error’. They opened it out of the mail and on a shared file station. It makes no difference. The strange thing: the macros using autofilter are working, the macros using pivot (either filtering or changing) doesn’t work. We use all Excel 2010 and work for the same company. Changing macro security doesn’t make a difference.

At the bottom of this forum message is one of my macros which isn’t working. Sorry for the Dutch language in the macro (‘draaitabel’ is the dutch word for pivot).

Explanation of the macro:
I have different hierarchy levels which I want to change in my pivot table. In this example I want to take away all levels (i.e. ‘C COUNTRY H’, ‘L LOCATION H’ etc.) and put in the team level (‘T TEAM H’). After that I change the color of the button, so I can see which button is selected. In my dashboard level ‘F FLOOR H’ is now selected. When I run the query I get an error message at: ‘ActiveSheet.PivotTables("Draaitabel1").CubeFields( _
"[Organisatie].[F FLOOR H]").Orientation = xlHidden’

Hopefully somebody can help me!

Macro:
Sub Niveau_Team()
ActiveSheet.PivotTables("Draaitabel1").CubeFields( _
"[Organisatie].[C COUNTRY H]").Orientation = xlHidden
ActiveSheet.PivotTables("Draaitabel1").CubeFields( _
"[Organisatie].[L LOCATION H]").Orientation = xlHidden
ActiveSheet.PivotTables("Draaitabel1").CubeFields( _
"[Organisatie].[F FLOOR H]").Orientation = xlHidden
ActiveSheet.PivotTables("Draaitabel1").CubeFields( _
"[Organisatie].[T TEAM H]").Orientation = xlHidden
ActiveSheet.PivotTables("Draaitabel1").CubeFields( _
"[Organisatie].[E NAME]").Orientation = xlHidden
With ActiveSheet.PivotTables("Draaitabel1").CubeFields( _
"[Organisatie].[T TEAM H]")
.Orientation = xlColumnField
.Position = 1
End With
‘Here starts the macro changing the button colors, depending on the button choice.
ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
With Selection.ShapeRange.ThreeD
.SetPresetCamera (msoCameraOrthographicFront)
.RotationX = 0
.RotationY = 0
.RotationZ = 0
.FieldOfView = 0
.LightAngle = 308
.PresetLighting = msoLightRigChilly
.PresetMaterial = msoMaterialClear
.Depth = 0
.ContourWidth = 0
.BevelTopType = msoBevelCircle
.BevelTopInset = 6
.BevelTopDepth = 5
.BevelBottomType = msoBevelNone
End With
Selection.ShapeRange.Shadow.Visible = msoFalse
Selection.ShapeRange.Line.Visible = msoFalse
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.5
.Transparency = 0
.Solid
End With
ActiveSheet.Shapes.Range(Array("Rectangle 3")).Select
With Selection.ShapeRange.ThreeD
.SetPresetCamera (msoCameraOrthographicFront)
.RotationX = 0
.RotationY = 0
.RotationZ = 0
.FieldOfView = 0
.LightAngle = 308
.PresetLighting = msoLightRigChilly
.PresetMaterial = msoMaterialClear
.Depth = 0
.ContourWidth = 0
.BevelTopType = msoBevelCircle
.BevelTopInset = 6
.BevelTopDepth = 5
.BevelBottomType = msoBevelNone
End With
Selection.ShapeRange.Shadow.Visible = msoFalse
Selection.ShapeRange.Line.Visible = msoFalse
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.5
.Transparency = 0
.Solid
End With
ActiveSheet.Shapes.Range(Array("Rectangle 2")).Select
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset39
ActiveSheet.Shapes.Range(Array("Rectangle 2")).Select
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.Solid
End With

End Sub