VBA code for using a cell value to change pivot filter

[y5f]

Good day: I am trying to change a pivot filter field by pointing it to a cell value in a sheet.
using the below code :

Please Login or Register  to view this content.

Where value in D1 is September. so dt = September
getting "run-Time error '1004':"

the pivot filter field is stored in a cube which is the source of the data
when i record changing the fields manually i get below code:

Please Login or Register  to view this content.

so with recording it seems it uses [Time].[Month Name].&[April]".
tried changing dt to say &[September] and still nothing.


also tried the below code and it runs but selects everything under the month name:

Please Login or Register  to view this content.

EDIT NOTE: this is using OLAP fields so I think I am going wrong with PivotItem.Name property

any ideas here on where I am going wrong?

Thanks

[dflak]

If you just want a single value. You can use

Please Login or Register  to view this content.

This works OK for text values. However, there is a caveat. If you do this and the value you are trying to set does not exist in the source data, then you will break the pivot table. Suppose you had "Company A" already in the filter and you use to the code to "select" "Company B" and Company B does not exist in the data. Then the pivot table will assign Company B to replace Company A in the filter list. Company A will not appear in the filter list and if you select Company B, you will get the former Company A data.

So, you need to do a check. While you have the value in the cell, in another cell do a COUNTIF() against the source data to see if that value exists. If the count is greater than zero, you can set the filter. If it is zero, skip setting the filter.

Pivot table filters work on strings, so if you want True or False, you need to pass "True" or "False." Likewise, date values need to be passed as their string equivalents in the format shown in the pivot filter.

The issue with trying to change the filter the way you are changing it (which is the right idea) is that a pivot table must always have at least one item selected. If you get to a condition where all the visible are false, the code will fail. In other words, you have 5 items and they are True, True, True, False, False and you want to change them to False, False, False, True, True. The code will work until it hits the third item. I has no problem turning the first two to false but when you try to turn the thrid item to false, all the items are false and Excel doesn't like that. It does not know that you intend to turn a later item to True.

So you will have code prior to the loop you have and turn everything ON, then loop and turn off those items you don't want.

I can explain better if you can provide a sample workbook.



Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[y5f]

thanks for the above.
i found a solution that works. it looks like this:
Sub pvtdate()

Dim yr As String
yr = Sheets("pvt").Range("M8").Value
Dim y As String
y = "[Time].[Year].&[" & yr & "]"

Sheets("a").PivotTables("PivotTable5").PivotFields( _
"[Time].[Year].[Year]").VisibleItemsList = Array( _
y)
Sheets("b").PivotTables("PivotTable5").PivotFields( _
"[Time].[Year].[Year]").VisibleItemsList = Array( _
y)
Sheets("c").PivotTables("PivotTable5").PivotFields( _
"[Time].[Year].[Year]").VisibleItemsList = Array( _
y)

Dim mt As String
mt = Sheets("Macros").Range("M7").Value
Dim x As String
x = "[Time].[Month Name].&[" & mt & "]"

Sheets("a").PivotTables("PivotTable5").PivotFields( _
"[Time].[Month Name].[Month Name]").VisibleItemsList = Array( _
x)
Sheets("b").PivotTables("PivotTable5").PivotFields( _
"[Time].[Month Name].[Month Name]").VisibleItemsList = Array( _
x)
Sheets("c").PivotTables("PivotTable5").PivotFields( _
"[Time].[Month Name].[Month Name]").VisibleItemsList = Array( _
x)



End Sub