Hi all,

I have a huge Excel file (75 MB) with around 60 pivot tables and 25 diagrams. I also have a few slicers. Currently I have to set the slicers manually (first the slicer for the first month, than the slicer for the first productnr (in reality, there is one more slicer)). Then I have to wait around 10 seconds until the diagrams are automatically generated. After that, I save the file as a pdf and I restart the proces by selecting the second product, and after that the second month, etc.

Because this takes me a few hours of brainless clicking and saving files, I would like to automatize this proces. I tried to make a macro, but it is extremely slow (around 30 minutes for selecting one month, one product and saving the file). Sometimes I get an error after 30 minutes. I think, Excel manually recalculates everything after the macro selects another item within a slicer. Does anybody know a solution for this?

A simplified version of the macro (in reality, there are around 50 items within each slicer, and there is one additional slicer):


Sub Makro_test()
'
' Makro_test Makro
'

'
With ActiveWorkbook.SlicerCaches("Slicer_Month")
.SlicerItems("2.2013").Selected = True
.SlicerItems("10.2013").Selected = False
.SlicerItems("11.2012").Selected = False
.SlicerItems("11.2013").Selected = False
.SlicerItems("12.2013").Selected = False
.SlicerItems("3.2013").Selected = False
.SlicerItems("4.2013").Selected = False
.SlicerItems("5.2013").Selected = False
.SlicerItems("6.2013").Selected = False
.SlicerItems("7.2013").Selected = False
.SlicerItems("8.2013").Selected = False
.SlicerItems("9.2013").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Number")
.SlicerItems("1").Selected = True
.SlicerItems("2").Selected = True
.SlicerItems("3").Selected = False
.SlicerItems("4").Selected = False
.SlicerItems("5").Selected = False
End With
ChDir _
"C:\Testdir"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Testdir\macro_test666.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
ActiveCell.Offset(19, 2).Range("A1").Select
End Sub