Results 1 to 10 of 10

VBA help to update pivottable date filter with a single cell value date

This is a discussion on VBA help to update pivottable date filter with a single cell value date within the Excel Questions forums, part of the Question Forums category; My first post.... I have a single worksheet with multiple pivot tables. They all have a date range filter. I ...

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    5

    Default VBA help to update pivottable date filter with a single cell value date

    My first post....

    I have a single worksheet with multiple pivot tables. They all have a date range filter. I want to insert vba code that will change the date filter for all the pivot tables on the sheet when I change a date in a single cell on the same sheet.

    I am using Excel 2007 on Windows XP. I am pretty new to pivot tables, as well as VBA, but I'm learning quickly. The pivot tables I am working with have been inherited, so I didn't create them myself.

    I've tried a few solutions I've found here and on the net, but they all vary greatly and didn't work. So I thought I'd start from scratch here.

    Any suggestions? It seems this should be simple using pivotfilters.add type, but I'm stuck.

    Thanks.

  2. #2
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,786

    Default Re: VBA help to update pivottable date filter with a single cell value date

    Hi Mary and Welcome to the Board,

    This thread has example code that should do what you describe.

    Pivot table with data validation

    PivotFilters.Add works a little differently. Instead of making individual PivotItems Visible or Hidden, it uses a criteria string. That function would be a better fit if you wanted all dates after a specified date, or between a pair of dates.

    Just ask if you need any help adapting the code for your purpose.
    Using Excel 2010, 2013

  3. #3
    New Member
    Join Date
    Jan 2012
    Posts
    5

    Default Re: VBA help to update pivottable date filter with a single cell value date

    Wow, that worked great! Thanks so much. I would never have been able to create that.

    Question....

    So I added to the vba code extra statements to have the same one cell date inserted as the filter to different date fields in four different pivot tables on the sheet. I need to go further with the same code to include around 50 different pivot tables in the sheet, all with different filter date fields, which all need to be changed to the date that's in cell B1.

    The code is below. I changed it to include 4 pivot tables, each with a different date field name. Is there an easier way to do this, rather than adding dim statements for all 50 date fields, field name definitions, then call filter statements for each pivot table and field? Is there a way to combine these three tasks into one statement per pivot table?

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim aField As String
        Dim bField As String
        Dim cfield As String
        Dim dfield As String
        Dim sDV_Address As String
        Dim ptTables As PivotTables
     
        aField = "Shipped Date"  'Field Name
        bField = "Image Audit Date"  'Field Name
        cfield = "Imaged Date"  'Field Name
        dfield = "QC Date"  'Field Name
        sDV_Address = "B1" 'Cell with date to select filter item.
        With ActiveSheet
            If Intersect(Target, Range(sDV_Address)) Is Nothing Or _
                Target.Cells.Count > 1 Then Exit Sub
     
            On Error GoTo CleanUp
            Application.EnableEvents = False
     
            Call Filter_PivotField( _
                pvtField:=.PivotTables("PivotTable46").PivotFields(aField), _
                    vItems:=Target.Value)
            Call Filter_PivotField( _
                pvtField:=.PivotTables("PivotTable47").PivotFields(bField), _
                    vItems:=Target.Value)
            Call Filter_PivotField( _
                pvtField:=.PivotTables("PivotTable48").PivotFields(cfield), _
                    vItems:=Target.Value)
            Call Filter_PivotField( _
                pvtField:=.PivotTables("PivotTable49").PivotFields(dfield), _
                    vItems:=Target.Value)
        End With
     
    CleanUp:
        Application.EnableEvents = True
    End Sub
    Thanks so much!

  4. #4
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,786

    Default Re: VBA help to update pivottable date filter with a single cell value date

    Maybe something like this that steps through an Array of PivotTable names and associated Fields.

    This is untested, but it will give you an idea of one approach.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim sDV_Address As String
        Dim ptTables As PivotTables
        Dim vFields As Variant, vPTNumbers As Variant
        Dim i As Long
          
        vPTNumbers = Array("46", "47", "48", "49")
        
        vFields = Array("Shipped Date", "Image Audit Date", _
                        "Imaged Date", "QC Date")
      
        sDV_Address = "B1" 'Cell with date to select filter item.
        With ActiveSheet
            If Intersect(Target, Range(sDV_Address)) Is Nothing Or _
                Target.Cells.Count > 1 Then Exit Sub
     
            On Error GoTo CleanUp
            Application.EnableEvents = False
     
            For i = LBound(vPTNumbers) To UBound(vPTNumbers)
                Debug.Print vPTNumbers(i) & " --> " & vFields(i) 'for testing
                Call Filter_PivotField( _
                    pvtField:=.PivotTables("PivotTable" & vPTNumbers(i)) _
                        .PivotFields(vFields(i)), _
                        vItems:=Target.Value)
            Next i
        End With
     
    CleanUp:
        Application.EnableEvents = True
    End Sub
    As an alternative to coding the Variant Array values into the VBA procedure,
    you might find it easier to maintain if you make a table in your worksheet that has all the pairs side-by-side.
    This procedure could be modified to read in the values in that table range.
    Using Excel 2010, 2013

  5. #5
    New Member
    Join Date
    Jan 2012
    Posts
    5

    Smile Re: VBA help to update pivottable date filter with a single cell value date

    I agree with you...a separate table on the sheet would be better, cleaner. So how would the code be modified to remove the array and have it point to the pivot table/date field table?

  6. #6
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,786

    Default Re: VBA help to update pivottable date filter with a single cell value date

    Quote Originally Posted by Mary2012 View Post
    I agree with you...a separate table on the sheet would be better, cleaner. So how would the code be modified to remove the array and have it point to the pivot table/date field table?
    You could try this. I assumes you have a range named "FieldTable" on Sheet1 with that range having PivotTable Numbers in the first column, and
    Field Names in the second. The range should not include any headers.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim sDV_Address As String
        Dim vFieldTable() As Variant
        Dim i As Long
      
        sDV_Address = "B1" 'Cell with date to select filter item.
        With ActiveSheet
            If Intersect(Target, Range(sDV_Address)) Is Nothing Or _
                Target.Cells.Count > 1 Then Exit Sub
     
            On Error GoTo CleanUp
            Application.EnableEvents = False
            
            vFieldTable = Application.Transpose(Sheets("Sheet1") _
                .Range("FieldTable"))
     
            For i = LBound(vFieldTable, 2) To UBound(vFieldTable, 2)
                Call Filter_PivotField( _
                    pvtField:=.PivotTables("PivotTable" & vFieldTable(1, i)) _
                        .PivotFields(vFieldTable(2, i)), _
                        vItems:=Target.Value)
            Next i
        End With
     
    CleanUp:
        Application.EnableEvents = True
    End Sub
    Using Excel 2010, 2013

  7. #7
    New Member
    Join Date
    Jan 2012
    Posts
    5

    Default Re: VBA help to update pivottable date filter with a single cell value date

    This looks great. So with this new code, do I need to make any changes to the public function filter (Filter_PivotField) in Module 1, which I added with the original code you gave me?

  8. #8
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,786

    Default Re: VBA help to update pivottable date filter with a single cell value date

    Quote Originally Posted by Mary2012 View Post
    This looks great. So with this new code, do I need to make any changes to the public function filter (Filter_PivotField) in Module 1, which I added with the original code you gave me?
    Nope.
    Using Excel 2010, 2013

  9. #9
    New Member
    Join Date
    Jan 2012
    Posts
    5

    Default Re: VBA help to update pivottable date filter with a single cell value date

    You are awesome! Thanks so much for your help! It took me a while to build the pivot table list - we have about 130 pivot tables on the sheet - but once I had everything in place it worked like a charm. I'm new on the job and scored a few points with my new co-workers, plus I learned a lot in the process. Thanks again!

  10. #10
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,786

    Default Re: VBA help to update pivottable date filter with a single cell value date

    You're welcome and thanks for the feedback!
    Using Excel 2010, 2013

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com