Updating a Pivot Table Filter, and a Filter in a Pivot Table Field, Based On a Value in a Cell In Another Sheet

[icspots]

Greetings VBA Experts!

I am new to VBA and this is my first time posting to this forum. I have searched for the past two days for a solution to this problem, but have been unable to find an example that matches exactly what I'm trying to do, and am not savvy enough with VBA to modify the solutions I've found to fit my needs.

In short based on a simplified example:

I have a workbook with multiple sheets. The first sheet ("Parameters") contains information that is manually updated each month and used in formulas throughout the workbook. The cell that I am interested in is "B1" where the date that the workbook is updated is entered. The cell is named "DateUpdated".

The second sheet (Pivot with Filter) contains a pivot table with a filter on the "Date" field. When the "Date Updated" cell (B1) is updated each month on the Paramaters tab, I would like the pivot table to automatically filter the table based on the new value. I was able to get this to work when the cell reference was on the same sheet as the pivot table, but when I moved it to the Parameters tab I could not figure out how to correctly reference the Parameters sheet in the Range call. The code still "works" if I disable the Target line by changing it to a comment, but I have to update the DateUpdated cell and then click on the cell with the filter in the pivot table before it refreshes. Here is the code with the "Target" line where the error occurs (Run-time error '1004': Method 'Range'of object_Worksheet' failed) in red:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'B1 on the Parameters tab is touched

    If Intersect(Target, Range("DateUpdated")) Is Nothing Then Exit Sub
 
'Set the Variables to be used
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewDate As String
 
'Here you amend to suit your data
    Set pt = Worksheets("Pivot with Filter").PivotTables("PivotTable1")
    Set Field = pt.PivotFields("Date")
    NewDate = Format(Worksheets("Parameters").Range("B1").Value, "m/d/yyyy")
 
'This updates and refreshes the PIVOT table
    With pt
    Field.ClearAllFilters
    Field.CurrentPage = NewDate
    pt.RefreshTable
    End With
 
End Sub

How do I modify the line below to correctly reference cell B1 on the Parameters sheet?

    If Intersect(Target, Range("DateUpdated")) Is Nothing Then Exit Sub

This example is close to what I'm trying to do, but instead of using the Date field as a filter for the pivot table, I really want the Date field to be included in the pivot table, and then to apply a filter on the date field to display data for dates that are LESS than the value entered in the DateUpdated cell. I suspect this is accomplished using something like the following (where PivotField has been defined as the "Date" field), but don't know how to modify the code above to include it.

<code class="gmail-lang-vb gmail-hljs gmail-vbnet">PivotField.PivotFilters.Add FilterType:= xlValueIsLessThan DataField:= MyPivotField2</code>

Thank you for any insights you can offer to point me in the right direction!

Kim

 

[icspots]

An update to my earlier post...

I solved the first part of my problem by simply moving the code to the Parameters tab. The code now reads:

[FONT=courier new]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'B1 or B2 on the Parameters tab is touched

    If Intersect(Target, Range("B1:B2")) Is Nothing Then Exit Sub
 
'Set the Variables to be used
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewDate As String
 
'Here you amend to suit your data
    Set pt = Worksheets("Pivot with Filter").PivotTables("PivotTable1")
    Set Field = pt.PivotFields("Date")
    NewDate = Format(Worksheets("Parameters").Range("B1").Value, "m/d/yyyy")
 
'This updates and refreshes the PIVOT table
    With pt
    Field.ClearAllFilters
    Field.CurrentPage = NewDate
    pt.RefreshTable
    End With
 
End Sub[/FONT]

 

[icspots]

I was finally able to filter the Date field in the pivot table on the "Pivot with Field" tab using the code below. I'm sharing it here in case it's helpful to someone else.

[FONT=courier new]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'B1 or B2 on the Parameters tab is touched

    If Intersect(Target, Sheets("Parameters").Range("B1:B2")) Is Nothing Then Exit Sub

    Dim NewDate As Date
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem

    Application.ScreenUpdating = False
    On Error Resume Next

    NewDate = Sheets("Parameters").Range("B1").Value

    Set pt = Worksheets("Pivot with Field").PivotTables(1)
    Set pf = pt.PivotFields("Date")

    pt.ManualUpdate = True

    pf.EnableMultiplePageItems = True

    For Each pi In pf.PivotItems
        pi.Visible = True
    Next pi

    For Each pi In pf.PivotItems
        If pi.Value > NewDate Then
        pi.Visible = False
    End If
    Next pi

    Application.ScreenUpdating = False
    pt.ManualUpdate = False

    Set pf = Nothing
    Set pt = Nothing

End Sub

[/FONT]