Update pivot table based on a date

Question

I want to update a pivot table based on a date in another cell. Each time it updates the table, i want it to only use the new dates that are in. the following code i have puts the dates in, but just keeps adding them to the old dates. i want to deselect the old dates and only put in the new dates.

Sheets("sheet3").Select

Dates = Range("aa2")

For x = 2 To Worksheets.Count

Sheets(x).Activate

    'ActiveSheet.PivotTables("PivotTable4").PivotFields("DATE ENTERED").CurrentPage _

        = "(All)"

    With ActiveSheet.PivotTables("PivotTable4").PivotFields("DATE ENTERED")

                .PivotItems(Dates).Visible = True

    End With

Next

Answer 1

Here is some code to do what I think you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim isect As Range

    Set isect = Application.Intersect(Target, Range("AA2"))

    If Not isect Is Nothing Then

        If Target > 0 Then

            With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")

                .ClearAllFilters

                .PivotFilters.Add Type:=xlAfterOrEqualTo, Value1:=Target

            End With

        End If

    End If

End Sub

Adjust the pivottable name and field name to match your pivot table

Press Alt+F11 double-click the name of the sheet with the pivot table in the top left corner of the VBE screen, copy the above code into the code sheet on the right

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

Answer 2

bHide = Evaluate(strDate & strCri)

this is what i get when i try to run it.

Answer 3

Sub HideByCriteria()

Dim pt As PivotTable, pi As PivotItem

Dim iDate As Long

Dim strCri As String

Dim bHide As Boolean

Dim xlCalc As XlCalculation

    strCri = Sheets("sheet3").Range("aa2")

    If strCri = vbNullString Then Exit Sub

    strCri = Trim(strCri)

   

    For x = 2 To Worksheets.Count

        Sheets(x).Activate

        Set pt = ActiveSheet.PivotTables("PivotTable4")

        pt.ManualUpdate = True

        With Application

               xlCalc = .Calculation

               '.Calculation = xlCalculationManual

               '.ScreenUpdating = False

        End With

        For Each pi In pt.PivotFields("DATE ENTERED").PivotItems

            iDate = pi

            bHide = Evaluate(iDate & strCri)

            pi.Visible = bHide

        Next pi

    Next

    pt.ManualUpdate = False

    With Application

            .Calculation = xlCalc

            .ScreenUpdating = True

     End With

End Sub

 

Answer 4

I'm asking it to update based on the date that will be in cell aa2. so whatever value i give to aa2 will be the date that is used to update the pv tables.

Answer 5

Are you asking for the code to show dates >= the value in cell AA2?

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire