Er zijn 14 tabellen en grafieken, allemaal gekoppeld aan een eigen slicer. Onderstaande code dient voor als er geen data in het bestand aanwezig was, de slicers verliezen dan hun selectie en geven dus allemaal de waarde van alle vestigingen weer. Dat wil ik niet maar wil ook niet handmatig moeten kiezen.
Onderstaande code is opgenomen via recorder maar er moet betere code dan deze zijn om een slicer per vestiging te selecteren. Ik weet niet hoe, kan iemand helpen?
Onderstaande code is opgenomen via recorder maar er moet betere code dan deze zijn om een slicer per vestiging te selecteren. Ik weet niet hoe, kan iemand helpen?
Code:
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging")
.SlicerItems("Nummer1").Selected = True
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer13").Selected = False
.SlicerItems("Nummer14").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging1")
.SlicerItems("Nummer2").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer13").Selected = False
.SlicerItems("Nummer14").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging2")
.SlicerItems("Nummer3").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer13").Selected = False
.SlicerItems("Nummer14").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging3")
.SlicerItems("Nummer4").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer13").Selected = False
.SlicerItems("Nummer14").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging4")
.SlicerItems("Nummer6").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer13").Selected = False
.SlicerItems("Nummer14").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging5")
.SlicerItems("Nummer11").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer13").Selected = False
.SlicerItems("Nummer14").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging6")
.SlicerItems("Nummer7").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer13").Selected = False
.SlicerItems("Nummer14").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging61")
.SlicerItems("Nummer8").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer14").Selected = False
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer13").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging7")
.SlicerItems("Nummer9").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer13").Selected = False
.SlicerItems("Nummer14").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging8")
.SlicerItems("Nummer10").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer13").Selected = False
.SlicerItems("Nummer14").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging9")
.SlicerItems("Nummer12").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer13").Selected = False
.SlicerItems("Nummer14").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging10")
.SlicerItems("Nummer13").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer14").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Vestiging11")
.SlicerItems("Nummer14").Selected = True
.SlicerItems("Nummer1").Selected = False
.SlicerItems("Nummer2").Selected = False
.SlicerItems("Nummer3").Selected = False
.SlicerItems("Nummer4").Selected = False
.SlicerItems("NUMMER5").Selected = False
.SlicerItems("Nummer6").Selected = False
.SlicerItems("Nummer7").Selected = False
.SlicerItems("Nummer8").Selected = False
.SlicerItems("Nummer9").Selected = False
.SlicerItems("Nummer10").Selected = False
.SlicerItems("Nummer11").Selected = False
.SlicerItems("Nummer12").Selected = False
.SlicerItems("Nummer13").Selected = False
End With