Results 1 to 10 of 10

Thread: Filtering all pivot tables using the same criteria

  1. #1
    Join Date
    Jan 2011
    Posts
    59

    Filtering all pivot tables using the same criteria

    Well I have installed Windows 7 on the computer of mine. also I have installed Microsoft Office 2007 on the computer of mine. I am having some data on the sheet1 and I have made 4 Pivot tables using data which is stored on the four different data sheet. I am having Customer in the report filter on the all the pivot tables. I am looking to view all the four Pivot tables by simply filtering with the same customer name. let me know if you are having any idea to filter the single sheet using the all the pivot table filter. Thanks a lot in advance.

  2. #2
    Join Date
    Jun 2009
    Posts
    1,518

    Re: Filtering all pivot tables using the same criteria

    Looking at the situation which you have provided over here I am thinking that the thing which you are looking to achieve can be get by using the Macro. Well according to me you should make an attempt to record a macro so that you need to see that there is any modification in to the pivot table in which the code is generated. Using you need to little bit research work do add a macro code. I can help you out if you can provide all the details regarding the pivot tables which you are wanted to filter using the same criteria.

  3. #3
    Join Date
    Nov 2008
    Posts
    1,514

    Re: Filtering all pivot tables using the same criteria

    Well I am recommending the following code which you should use to get the filter the all the pivot tables using the same criteria.
    Code:
    Sheets("Table1-value").Select
        ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSTOMER NAME"). _
            CurrentPage = "ACC"
    The above mentioned code would work for the single selection at a time for the all the pivot tables for the single word for the filtering condition. so you should try the above mentioned thing which I have suggested and let me know the results for the same.

  4. #4
    Join Date
    Mar 2009
    Posts
    1,360

    Re: Filtering all pivot tables using the same criteria

    I am providing the following code which you can use. You should place the following code on the main filter sheet.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       
       Dim sValue        As Variant
       Dim sField        As String
       
       sField = "CUSTOMER NAME"
       sValue = Target.Value
       
       Application.ScreenUpdating = False
       Application.Calculation = xlCalculationManual
       Call doClearFilter("Table1", "PivotTable1", sField)
       Call doPivotFilter("Table1", "PivotTable1", sField, sValue)
       
       Call doClearFilter("Locations", "PivotTable8", sField)
       Call doPivotFilter("Locations", "PivotTable8", sField, sValue)
       
       Call doClearFilter("Category", "PivotTable10", sField)
       Call doPivotFilter("Category", "PivotTable10", sField, sValue)
       Application.ScreenUpdating = True
       Application.Calculation = xlCalculationManual
       Application.CalculateFull
       
    End Sub
    Now you should add another module and put the following code in the same.
    Code:
    Sub doClearFilter(sSheetName As String, sPivotTableName As String, sPivotField As String)
       Sheets(sSheetName).PivotTables(sPivotTableName).PivotFields(sPivotField).ClearAllFilters
    End Sub
    
    Sub doPivotFilter(sSheetName As String, sPivotTableName As String, sFieldName As String, sValue As Variant)
    
       Dim vItem         As Variant
       
       For Each vItem In Sheets(sSheetName).PivotTables(sPivotTableName).PivotFields(sFieldName).PivotItems
         vItem.Visible = (vItem = sValue)
       Next
    
    End Sub
    Let me know whether above mention code helped you or not.

  5. #5
    Join Date
    Nov 2008
    Posts
    1,259

    Re: Filtering all pivot tables using the same criteria

    Well thanks a lot for the prompt replies of yours . I have used the code which you have provided over here. It is showing an error message on the screen which is stating that it is need have an object. When I do the following modification in the code.
    Code:
    sValue = Range("a4").Value
    after doing the above mentioned thing I am getting following error message on the screen.
    Code:
    Unable to set the visible property of the pivotitem class and highlight the below code
    For Each vItem In Sheets(sSheetName).PivotTables(sPivotTableName).PivotFields(sFieldName).PivotItems
         vItem.Visible = (vItem = sValue)
    
    in doPivotFilter() function.
    Let me know if you are having any fix to solve the problem of mine.

  6. #6
    Join Date
    Nov 2008
    Posts
    1,185

    Re: Filtering all pivot tables using the same criteria

    The code which is provided by the all the users would work in the following Sub.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    You need to convert the same on a single button call. In case there is any problem with the ‘sValue = Target.Value’ then you need to fix the same. After fixing the problem you will not have an issue any issue.

  7. #7
    Join Date
    Nov 2008
    Posts
    1,221

    Re: Filtering all pivot tables using the same criteria

    Recently I have created a Pivot table using the data table using the three column Data, Sales and also Customers. I was looking to change the date on the basis of the value which is being stored in the cell which is named as Date. The pivot table is placed on the another worksheet and you will b able to change the date by using the spin button which is located in the active worksheet. I have used the following code which you can use.
    Code:
    Sub ptDate()
    
    Dim pt As PivotTable
    Set pt = Worksheets("Pivot Tables").PivotTables("PivotTable1")
    
    pt.PivotFields("Date").ClearAllFilters
    pt.PivotFields("Date").CurrentPage = Range("Date")
    
    End Sub
    I am hoping that it will help you out to fix the matter of yours in any manner.

  8. #8
    jasonwoolen13 Guest

    Re: Filtering all pivot tables using the same criteria

    In this particular case, I want to share something that can be used to meet requirements of the mine. and because a user told you to use the VLOOKUP and have also set the following formula = VLOOKUP (A: A, Sheet2 A: A, 1, FALSE). If there is no overlap between the values ​​that will receive if there is no match, then it will show # N / A. Finally it is necessary to filter all matching values ​​and simply copy the same in the new sheet. I hope that will help meet the requirement of yours.

  9. #9
    richimarker77 Guest

    Re: Filtering all pivot tables using the same criteria

    Well thank you very much for the responses of the system of their own. I used the code you have provided over here. It shows an error message on the screen indicating that you must have an object.

  10. #10
    WilsJackO Guest

    Re: Filtering all pivot tables using the same criteria

    I was looking for a date change depending on the worth stored in the cell is called the date. The pivot table is placed on another sheet & b able to change the date using the spin button that is on the active worksheet.

Similar Threads

  1. Combine 2 Pivot Tables into 1 Master Workbook?
    By DANIEL 602 in forum MS Office Support
    Replies: 1
    Last Post: 04-02-2013, 11:51 AM
  2. How to merge 2 pivot tables in Excel
    By Rao's in forum Windows Software
    Replies: 2
    Last Post: 07-01-2012, 05:18 PM
  3. Criteria to become CBI Officer
    By Markesh in forum Education Career and Job Discussions
    Replies: 8
    Last Post: 20-10-2010, 05:30 PM
  4. Issue while inserting pivot Tables in Excel 2003
    By Clemens in forum Windows Software
    Replies: 3
    Last Post: 29-04-2009, 04:30 PM
  5. Pivot Tables in Excel 2007
    By dixiesky in forum Windows Software
    Replies: 4
    Last Post: 10-04-2009, 11:40 AM

Tags for this Thread

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
  •  
Page generated in 1,711,656,578.02294 seconds with 17 queries