PDA

View Full Version : Loop through array of buttons on a Pivot table



satyen
10-19-2013, 09:06 AM
Hello.

Please could someone help.

I have this code that selects each item/button on the pivot table - but the code I not efficient as i would like it loop to a hundred.


Sub Macro13()


ActiveWorkbook.Slicer("SlBook").ItemsList = _
Array( _
"[100].[DayNo].&[1]")

ActiveWorkbook.Slicer("SlBook").ItemsList = _
Array( _
"[100].[DayNo].&[1]", "[100].[DayNo].&[2]")

ActiveWorkbook.Slicer("SlBook").ItemsList = _
Array( _
"[100].[DayNo].&[1]", "[100].[DayNo].&[2]", _
"[100].[DayNo].&[3]")

ActiveWorkbook.Slicer("SlBook").ItemsList = _
Array( _
"[100].[DayNo].&[1]", "[100].[DayNo].&[2]", _
"[100].[DayNo].&[3]", "[100].[DayNo].&[4]")

End Sub

Aflatoon
10-21-2013, 04:30 AM
Perhaps:
Sub Macro13()
Dim aItems(99)
Dim n As Long
For n = 0 To 99
aItems(n) = "[100].[DayNo].&[" & n + 1 & "]"
Next n
ActiveWorkbook.Slicer("SlBook").ItemsList = aItems

End Sub