Selecting Multiple Values in a Slicer with VBA

[Chiemi Yokota]

Hello,

I have a slicer which value consists of Fiscal week (1 - 53). Each week when I refresh my powerpivot, I would like to select weeks from 1 thru last week.

The code is attached below, and it gets stuck at ".slicerItems(i) ..." line. Any help would be much appreciated!!!

Sub Test4()

Worksheets("Top 20 - YTD").Select

Dim CY_WK As Integer
CY_WK = Sheets("Note").Range("A2").Value

Dim i As Integer

With ActiveWorkbook.SlicerCaches("Slicer_theBookedWeek2")
    For i = 1 To CY_WK
     .SlicerItems(i).Selected = True
    Next i

End With

End Sub

Thank you!!!

 

[Hui]

Chiemi

Firstly, Welcome to the Chandoo.org Forums

Try:
.SlicerItems(trim(str(i))).Selected = True

If that doesn't help
can you upload your file?

 

[Chiemi Yokota]

Thank you for the warm welcome and great suggestion, Hui.

The code still get stuck at the line. The error says Slicer Items expression is not defined in context ...

I would like to upload the file but it has all the confidential sales number ...

What else can I try to make this code work?

Thank you!

 

[Hui]

Even when slicers have numbers they appear to be listed as Labels in the slicer and on my test data 1 was shown as "1"
up the top add

Dim Tempstr as String

Then add two lines

Tempstr = chr(34)+trim(str(i))+chr(34)
.SlicerItems(Tempstr).Selected = True

 

[Chiemi Yokota]

Thank you again ... it still doesn't work

I noticed that if I were to use Array function with .VisibleSlicerItemsList command, it works fine. But I am not proficient in VBA enough to convert this into the For i function so that all weeks from 1 up to the current week will be selected.

I tried to do the same using the .SlicerItems, but it doesn't recognize the value of i at all.

Dim i As Integer

i = 13

ActiveWorkbook.SlicerCaches("Slicer_theBookedWeek2").ClearManualFilter

ActiveWorkbook.SlicerCaches("Slicer_theBookedWeek2").VisibleSlicerItemsList = _
    Array("[Gross].[theBookedWeek].&[" & i & "]")

 

[Hui]

Chiemi

Just thinking about your problem

If you want to select all the Slicers, isn't that the same as clearing all the selections?

ActiveWorkbook.SlicerCaches("Slicer_theBookedWeek2").ClearManualFilter

 

[Hui]

The code below is working for me :

Dim i As Integer
Dim CY_WK As Integer
CY_WK = Sheets("Note").Range("A2").Value

ActiveWorkbook.SlicerCaches("Slicer_theBookedWeek2").ClearManualFilter

With ActiveWorkbook.SlicerCaches("Slicer_theBookedWeek2")
  For i = 1 To ActiveWorkbook.SlicerCaches("Slicer_theBookedWeek2").SlicerItems.Count
  If i <= CY_WK Then
  .SlicerItems(i).Selected = True
  Else
  .SlicerItems(i).Selected = False
  End If
  Next
   
End With

ActiveWorkbook.Sheets("Sheet1").PivotTables("pivotTable1").PivotCache.Refresh 'Not sure this line is needed

 

[Chiemi Yokota]

Thank yonu again. I don't think the issue is the code ... I don't know what it's doing on the back end, but each week value (1 - 53) which stored as integer in the table in the power pivot becomes something else when it is used in the slicer. I tried your str() or trim() along with the codes above, but nothing works ...

The only success I had was when I used the .VisibleSlicerItemsList. Even if I were to manually put the number in the string format, the code gets stuck with .SlicerItem command.

I'm thinking I can just add the msg box to pop up asking users to use Ctrl + current week value, so the cumulative weeks will be highlighted ...

 

[Hui]

Do you want to email me the file and I can review
Discretion guaranteed
Or
Just replace all names with A's etc

 

[Chiemi Yokota]

Hi Hui,

I tried to upload the file, but the powerpivot table is too big. It is 1.2 MB. Would it be possible to e-mail it to you? I deleted all the confidential material. I tried the conversation but it says I can't start the conversation with you ... So sorry. New to the site. Learning everything.

Thank you so very much for all your help.