Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 06-04-2011
Member
 
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.
Reply With Quote
  #2  
Old 06-04-2011
Member
 
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.
Reply With Quote
  #3  
Old 06-04-2011
Member
 
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.
Reply With Quote
  #4  
Old 06-04-2011
Member
 
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.
Reply With Quote
  #5  
Old 06-04-2011
Member
 
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.
Reply With Quote
  #6  
Old 07-04-2011
Member
 
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.
Reply With Quote
  #7  
Old 07-04-2011
Member
 
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.
Reply With Quote
  #8  
Old 07-04-2011
jasonwoolen13
 
Posts: n/a
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.
Reply With Quote
  #9  
Old 15-04-2011
richimarker77
 
Posts: n/a
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.
Reply With Quote
  #10  
Old 28-07-2011
WilsJackO
 
Posts: n/a
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.
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Filtering all pivot tables using the same criteria"
Thread Thread Starter Forum Replies Last Post
Combine 2 Pivot Tables into 1 Master Workbook? DANIEL 602 MS Office Support 1 04-02-2013 11:51 AM
How to merge 2 pivot tables in Excel Rao's Windows Software 2 07-01-2012 05:18 PM
Criteria to become CBI Officer Markesh Education Career and Job Discussions 8 20-10-2010 05:30 PM
Issue while inserting pivot Tables in Excel 2003 Clemens Windows Software 3 29-04-2009 04:30 PM
Pivot Tables in Excel 2007 dixiesky Windows Software 4 10-04-2009 11:40 AM


All times are GMT +5.5. The time now is 06:22 PM.