Update pivot table filters based on cell value

[PCRIDE]

When I run the macro, this vb clears the filter on that Sheet, but doesn't send the value from the Dashboard M6 field, which is a date. Any idea why its not working? Do I have the reference to the sheet correct?


Sub VisibleOneItem_01()


On Error Resume Next
Dim dt As String
dt = Sheets("Dashboard").Range("M6").Value
With Worksheets("Results").PivotTables("ResultsPivotTbl").PivotFields("cc-StartDate")

.ClearAllFilters
.PivotFilters.Add Type:=xlCaptionEquals, Value1:=dt
End With
End Sub

 

[Alex Blakenburg]

Is the filter in the Filter section of the table or the row section ?
If its in the Filter section try this:

pvFld.CurrentPage = CDate(dt)

If its in the row section try this:

pvFld.PivotFilters.Add2 Type:=xlSpecificDate, Value1:=dt

Note: in the 2nd one if Add2 doesn't work try Add, I think its excel version dependant with Add2 working from 2013 on.

 

[PCRIDE]

Is the filter in the Filter section of the table or the row section ?
If its in the Filter section try this:

pvFld.CurrentPage = CDate(dt)

If its in the row section try this:

pvFld.PivotFilters.Add2 Type:=xlSpecificDate, Value1:=dt

Note: in the 2nd one if Add2 doesn't work try Add, I think its excel version dependant with Add2 working from 2013 on.

Thanks, Its in the filters and tried the code and didn't work. I was searching around and came across one of my older posts, I am now using this code, which seems to work fine with multiple pivot tables, but how would I condense this for just one pivot table? Its failing at

Field2.CurrentPage = NewCat2

Basically I'm trying to change 3 filters in the pivot table filters so users can get the data based on a dashboard selection. I tried using pt1 across the board, that didn't work. I don't think I need to clear the filters each time, or that just removes what I just filtered. It does filter the Date field, which is the first Field, but fails on the second.
-------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Worksheets("Dashboard").Range("M6")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim pt3 As PivotTable

Dim Field1 As PivotField
Dim Field2 As PivotField
Dim Field3 As PivotField

Dim NewCat1 As String
Dim NewCat2 As String
Dim NewCat3 As String


'Here you amend to suit your data
Set pt1 = Worksheets("Results").PivotTables("ResultsPivotTbl")
Set pt2 = Worksheets("Results").PivotTables("ResultsPivotTbl")
Set pt3 = Worksheets("Results").PivotTables("ResultsPivotTbl")
Set Field1 = pt1.PivotFields("cc-StartDate")
Set Field2 = pt2.PivotFields("Direct")
Set Field3 = pt3.PivotFields("Code")
NewCat1 = Worksheets("Dashboard").Range("M6").Value
NewCat2 = Worksheets("Dashboard").Range("L6").Value
NewCat3 = Worksheets("Dashboard").Range("O6").Value

'This updates and refreshes the PIVOT table
With pt1
Field1.ClearAllFilters
Field1.CurrentPage = NewCat1
pt1.RefreshTable
End With
With pt2
Field2.ClearAllFilters
Field2.CurrentPage = NewCat2
pt2.RefreshTable
End With
With pt2
Field3.ClearAllFilters
Field3.CurrentPage = NewCat3
pt3.RefreshTable
ActiveWorkbook.RefreshAll
End With


End Sub

 

[Alex Blakenburg]

This won't fix it but your With statements aren't doing anything.
I am also unclear as to whether you tried using CDate on the date filter:
Did it fail using the CDate ?
If it did show me what is in M6 including what is in the formula bar.

With pt1
  Field1.ClearAllFilters
  Field1.CurrentPage = CDate(NewCat1)
  .RefreshTable             ' to use the with statement drop "pt1" reference here, the period tells it to use the pt1 from the With
End With

 

[PCRIDE]

This won't fix it but your With statements aren't doing anything.
I am also unclear as to whether you tried using CDate on the date filter:
Did it fail using the CDate ?
If it did show me what is in M6 including what is in the formula bar.

With pt1
  Field1.ClearAllFilters
  Field1.CurrentPage = CDate(NewCat1)
  .RefreshTable             ' to use the with statement drop "pt1" reference here, the period tells it to use the pt1 from the With
End With

I tried the CDate in the first block of code, that didn't work. In your second example, I tried it in the new code and its not making a difference. The Field 1 is working and changing the pivot table date (M6) on the dashboard. the code stops at Field 2.

Are you saying to not use pt1 ? I guess I'm not understanding. Basically I have one pivot table with 3 filters I need to change.

 

[PCRIDE]

I tried the CDate in the first block of code, that didn't work. In your second example, I tried it in the new code and its not making a difference. The Field 1 is working and changing the pivot table date (M6) on the dashboard. the code stops at Field 2.

Are you saying to not use pt1 ? I guess I'm not understanding. Basically I have one pivot table with 3 filters I need to change.

This works perfect as is. But only with 1 field. I need to add 2 more fields to this which are filters in the pivot table.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Worksheets("Dashboard").Range("M6")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt1 As PivotTable
Dim Field1 As PivotField
Dim NewCat1 As String

Set pt1 = Worksheets("Results").PivotTables("ResultsPivotTbl")
Set Field1 = pt1.PivotFields("cc-StartDate")
NewCat1 = Worksheets("Dashboard").Range("M6").Value

With pt1
Field1.ClearAllFilters
Field1.CurrentPage = CDate(NewCat1)
ActiveWorkbook.RefreshAll
End With
End Sub

 

[PCRIDE]

OK I GOT IT

This is now working. I update 3 pivot table filters from cells on another tab!

thanks for your help!! What does CDATE do? Not sure thats doing anything however.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Worksheets("Dashboard").Range("M6")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt1 As PivotTable
Dim Field1 As PivotField
Dim NewCat1 As String


'Here you amend to suit your data
Set pt1 = Worksheets("Results").PivotTables("ResultsPivotTbl")
Set Field1 = pt1.PivotFields("cc-StartDate")
NewCat1 = Worksheets("Dashboard").Range("M6").Value

Set pt1 = Worksheets("Results").PivotTables("ResultsPivotTbl")
Set Field2 = pt1.PivotFields("Direct")
NewCat2 = Worksheets("Dashboard").Range("O6").Value

Set pt1 = Worksheets("Results").PivotTables("ResultsPivotTbl")
Set Field3 = pt1.PivotFields("Code")
NewCat3 = Worksheets("Dashboard").Range("L6").Value

'This updates and refreshes the PIVOT table
With pt1
Field1.ClearAllFilters
Field1.CurrentPage = CDate(NewCat1)
Field2.CurrentPage = (NewCat2)
Field3.CurrentPage = (NewCat3)
ActiveWorkbook.RefreshAll
End With
End Sub

 

[Alex Blakenburg]

1) It would be safer to clear the filters before reapplying, if someone manually selects multiple filters on a field it may fail without it.
2) What is your Regional Date format ? Is it US mm/dd/yyyy ? It is possible that I only need to use it because our format is dd/mm/yyyy.
3) Since you are only using 1 pivot you don't need to have pt1,2,3
4) Since the 2nd With only uses pt1 once inside the with, it is probably not worth using the with at all and just have pt1.RefreshTable
5) I assume the code in the module for the sheet Worksheets("Dashboard"), so you don't actually need to have that in your code. When you put Range("M6").Value it assumes the active sheet. If you want to make it clearer you could use Me.Range("M6").Value where Me is a keyword referring to the Sheet that contains the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
'
If Intersect(Target, Worksheets("Dashboard").Range("M6")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt1 As PivotTable

Dim Field1 As PivotField
Dim Field2 As PivotField
Dim Field3 As PivotField

Dim NewCat1 As String
Dim NewCat2 As String
Dim NewCat3 As String


'Here you amend to suit your data
Set pt1 = Worksheets("Results").PivotTables("ResultsPivotTbl")

With pt1
    Set Field1 = .PivotFields("cc-StartDate")
    Set Field2 = .PivotFields("Direct")
    Set Field3 = .PivotFields("Code")
End With

NewCat1 = Worksheets("Dashboard").Range("M6").Value
NewCat2 = Worksheets("Dashboard").Range("L6").Value
NewCat3 = Worksheets("Dashboard").Range("O6").Value

'This updates and refreshes the PIVOT table
With pt1
    Field1.ClearAllFilters
    Field1.CurrentPage = CDate(NewCat1)
    Field2.ClearAllFilters
    Field2.CurrentPage = NewCat2
    Field3.ClearAllFilters
    Field3.CurrentPage = NewCat3
    .RefreshTable
End With

End Sub