VBA change pivot filter date with reference cell

[gauravkrgautam]

Hi Ppl,

I am trying to update my pivot table with macro on the basis of a cell reference. Pivot filter should select only one date i.e F6 and unselect all other values i.e. F5 & blanks.

and also like to know why the pivot filter drop down format in unchangeable as in sample file it is in DD-MMM-YY format while in workbook i am working on has format MM/DD/YYYY.
why is this gap


Many thanks in advance.

 

[Hui]

I would do two things

1. You don't need the value in cell F5 so clear it
2. add the following code to the Sheet4 code module in VBA

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F6")) Is Nothing Then Exit Sub
Application.EnableEvents = False
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = Range("F6").Text
Application.EnableEvents = True

End Sub

 

[jossy]

Hi Ppl,

I am trying to update my pivot table with macro on the basis of a cell reference. Pivot filter should select only one date i.e F6 and unselect all other values i.e. F5 & blanks.

and also like to know why the pivot filter drop down format in unchangeable as in sample file it is in DD-MMM-YY format while in workbook i am working on has format MM/DD/YYYY.
why is this gap


Many thanks in advance.

Here is a simple macro that actually works doing something similar. You can modify from this macro. I have a pivot table that contains in Column H the Order Fill Rate % of various finished goods. An Order Fill Rate of 98.5% or lower is considered no good in our company. This macro will go to Column H starting with Row 5 and go down row by row to check the fill rates. It will hide the rows that are good leaving visible those rows that require further analysis or action. It will keep doing this until it reaches the "last row"


Sub zSeries09_FilterPivotTable_ByFillRate98pt5()
'
' zSeries09_FilterPivotTable_ByFillRate98pt5 Macro
'

'
Application.Calculation = xlManual


Dim LR As String

Selection.SpecialCells(xlCellTypeLastCell).Select

LR = ActiveCell.Row

Application.Goto Reference:="R5C8"



Routine:

If ActiveCell.Row < LR Then GoTo TestError Else GoTo EndCom

TestError:

If IsError(ActiveCell) = True Then GoTo HideRow Else

TestBlank:

If IsEmpty(ActiveCell) = True Then GoTo HideRow Else

TestFillRate:

If ActiveCell.Value >= 0.985 Then GoTo HideRow Else

NoHide:

Application.Goto Reference:="R[1]C"

GoTo Routine

HideRow:

Selection.EntireRow.Hidden = True
Application.Goto Reference:="R[1]C"

GoTo Routine

EndCom:


Application.Calculation = xlAutomatic



End Sub