+ Reply to Thread
Results 1 to 2 of 2

Change Pivot Table Filter from Value in a Cell

  1. #1
    Registered User
    Join Date
    01-13-2011
    Location
    Glasgow
    MS-Off Ver
    Excel 2011 (Mac)
    Posts
    2

    Change Pivot Table Filter from Value in a Cell

    Hi, apologies if you have seen this one before but I've been through about 10 posts and walkthroughs and cant get this to work!!

    I'm a VBA newbie so don't have a great deal on knowledge on how to sort it but here's what I'm trying to do


    I have 6 Pivot tables on Sheet 3 that are built to update a few charts on Sheet 2.

    Sheet 1 contains all the raw data.


    I want the user to be able to type in the date into a cell on Sheet 2 (cell "K1") and for that to update the 'Date' filter in PivotTable4. The value for the date in the Pivot Table is "B3" (Sheet3)

    The code that I've been working on suggested activating a box to run the date change, to save it calculating all the time. I've got this cell as "N1". This is in Sheet 2, but I've not seen any mention of the sheet name in the code?

    Anyway, the debugger is highlighting the row I have put in bold.

    Can anyone point me in the right direction???? I'm sure once I have it working for this Pivot, I will be able to adapt it for all of them!!

    Hope all that makes sense!!



    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, Range("N1")) Is Nothing Then Exit Sub

    'Set the Variables to be used
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As String

    'Here you amend to suit your data
    Set pt = Worksheets("Sheet3").PivotTables("PivotTable4")
    Set Field = pt.PivotFields("Date")
    NewCat = Worksheets("Sheet3").Range("B3").Value

    'This updates and refreshes the PIVOT table
    With pt
    Field.ClearAllFilters
    Field.CurrentPage = NewCat
    pt.RefreshTable
    End With

    End Sub

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,085

    Re: Change Pivot Table Filter from Value in a Cell

    First of all, is the pivot table on Sheet3 actually called "PivotTable4?"

    Also I found that when it comes to filters, pivot tables treat dates like strings, so while 42,542 is June 21, 2016 to the rest of excel, putting that number into a filter won't work. The value you use must match the format of the value that the filter is expecting. So if it's 06/12/16 then you need to provide the string "06/12/16." On the other hand, if it's 6/21/2016 then you need to provide "6/21/2016."

    I am not sure if this is still an issue. However it is something to keep in mind when dealing with dates and pivot filters.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 07-31-2014, 12:56 PM
  2. [SOLVED] Using vba to change pivot table filter based on cell value in another worksheet
    By kidwispa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-05-2013, 11:10 AM
  3. Change pivot table filter with VBA based on cell values
    By ZeDoctor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2012, 09:03 AM
  4. Pivot table, assigning a cell wo be change the Pivot table filter
    By jwongsf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-28-2012, 05:00 PM
  5. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 AM
  6. Change pivot table report filter selection from a cell
    By nestorph in forum Excel General
    Replies: 4
    Last Post: 08-17-2011, 10:19 AM
  7. Change Pivot Table Filter Based on Cell Value
    By tohotspur in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-15-2010, 02:55 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1