martinibbo
asked on
Can you filter a pivot table based on another pivot tables results?
Hi,
I have two pivot tables and I want to be able to filter my second pivot table ("SOs") based on the first pivot tables ("POs") results using vba.
The code I have so far is as follows:
The issue with this however, is that each element in the array references the first pivot tables data source, e.g. [New_PO_2].[PO Number].&[10255], where the second pivot table uses a different source and would require the element changing to [New_SO_2].[PO Number].&[10255].
I am thinking something along the lines the following, but I cannot get it to work:
I would also need to do some error checking in case certain items were not found in the second pivot table so that they were ignored.
Thanks
I have two pivot tables and I want to be able to filter my second pivot table ("SOs") based on the first pivot tables ("POs") results using vba.
The code I have so far is as follows:
Dim MyArray As Variant
MyArray = ActiveSheet.PivotTables("POs").PivotFields("[New_PO_2].[PO Number].[PO Number]").VisibleItemsList
ActiveSheet.PivotTables("SOs").PivotFields("[New SO 2].[PO Number].[PO Number]").VisibleItemsList = MyArray
The issue with this however, is that each element in the array references the first pivot tables data source, e.g. [New_PO_2].[PO Number].&[10255], where the second pivot table uses a different source and would require the element changing to [New_SO_2].[PO Number].&[10255].
I am thinking something along the lines the following, but I cannot get it to work:
For i = LBound(MyArray) To UBound(MyArray)
MyArray(i, 1) = Replace(MyArray(i, 1), "[New_PO_2]", "[New_SO_2]")
Next i
I would also need to do some error checking in case certain items were not found in the second pivot table so that they were ignored.
Thanks
Have you considered using pivot slicers?
ASKER
I'm not sure how I would do this accross two pivots using different data sources - could you provide an example workbook / walkthrough / link?
I think that the problem is that the two pivot tables use different Connections as a Data Source.
I also need it to be automated so that when the first pivot is filtered by the customers name and the specific item number, the second pivot is filtered by the PO Numbers that customer has ordered (i.e. results of Pivot Table 1)
Thanks
I think that the problem is that the two pivot tables use different Connections as a Data Source.
I also need it to be automated so that when the first pivot is filtered by the customers name and the specific item number, the second pivot is filtered by the PO Numbers that customer has ordered (i.e. results of Pivot Table 1)
Thanks
Can you post your sample file.. Also i'm assuming this is what you are looking for you got pivot-1 and pivot-2..Now the values which you have selected in pivot-1..you only want to show those values in pivot-2.. Is this what you are looking for??
Saurabh...
Saurabh...
ASKER
Hi Saurabh,
It is difficult for me to post the actual file as the data held within is commercially sensitive, so I have created a basic one attached.
Pivot Source 1, is the raw data for PivotTable1 which is held in a workbook Query (Connection Only)
Pivot Source 2, is the raw data for PivotTable2 which is held in a workbook Query (Connection Only)
When PivotTable1 is filtered, I want the resulting PO Numbers to be the filters on PivotTable2 as shown in the "mocked up" desired result.
The Code within Module1 does not work because the source data is in different workbooks. i.e. the individual elements in the array point at different sources.
What you describe seems in line with my desired results, where the source data is two separate connections.
Is there not a way using vba to change each elements name within an array as described above, or perhaps to write the array to a worksheet, rename each element, then reload to the array?
Hope this helps.
Many thanks
EE---Pivot-Tables.xlsm
It is difficult for me to post the actual file as the data held within is commercially sensitive, so I have created a basic one attached.
Pivot Source 1, is the raw data for PivotTable1 which is held in a workbook Query (Connection Only)
Pivot Source 2, is the raw data for PivotTable2 which is held in a workbook Query (Connection Only)
When PivotTable1 is filtered, I want the resulting PO Numbers to be the filters on PivotTable2 as shown in the "mocked up" desired result.
The Code within Module1 does not work because the source data is in different workbooks. i.e. the individual elements in the array point at different sources.
What you describe seems in line with my desired results, where the source data is two separate connections.
Is there not a way using vba to change each elements name within an array as described above, or perhaps to write the array to a worksheet, rename each element, then reload to the array?
Hope this helps.
Many thanks
EE---Pivot-Tables.xlsm
ASKER
Ok, so I got there, but I would appreciate input whether there is a more efficient way of doing it rather than writing to a range first and testing that each item exists in pivot2.
code now looks like this:
I have reattached the excel sheet for reference.
EE---Pivot-Tables.xlsm
code now looks like this:
Sub test()
Dim MyArray As Variant
MyArray = ActiveSheet.PivotTables("PivotTable1").PivotFields("[Table1].[PO Number].[PO Number]").DataRange
LastRow = UBound(MyArray)
Range("L1:L" & UBound(MyArray)) = MyArray
Range("L1:L" & UBound(MyArray)).Select
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "[Table5].[PO Number].&[" & c.Value & "]"
If bFieldItemExists(c.Value) = False Then c.Clear
Next
Dim myArray2 As Variant
myArray2 = Application.Transpose(Range("L1: L" & LastRow).Value)
ActiveSheet.PivotTables("PivotTable2").PivotFields("[Table5].[PO Number].[PO Number]").VisibleItemsList = myArray2
Range("L1: L" & LastRow).Clear
Range("E1").Select
End Sub
Function bFieldItemExists(strName As String) As Boolean
Dim strTemp As String
On Error Resume Next
strTemp = ActiveSheet.PivotTables("PivotTable2").PivotFields("[Table5].[PO Number].[PO Number]").PivotItems(strName)
If Err = 0 Then bFieldItemExists = True Else bFieldItemExists = False
End Function
I have reattached the excel sheet for reference.
EE---Pivot-Tables.xlsm
Martin,
Caught in couple of things at the moment..Give me time till thursday will post you a solution which you going to love..
Saurabh...
Caught in couple of things at the moment..Give me time till thursday will post you a solution which you going to love..
Saurabh...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I guess then all good and like i said i don't understand it myself why it's happening because it should work..But glad able to make it work..
I believe then you are good to go on this..
Saurabh...
I believe then you are good to go on this..
Saurabh...
ASKER
Saurabh's efforts solved 99% of my question and without his input I would not have a solution. Thanks :)