Help regarding Pivot filters

sandeep koyya

New Member
Joined
Jun 28, 2015
Messages
2
I have two pivot tables Pivot info1 and pivotinfo2.They drag information from different excel sheets
They have common filters as shown in below
TABLE 1 TABLE2
[TABLE="width: 531"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Location[/TD]
[TD](All)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Location[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]GE/NON-GE[/TD]
[TD](All)[/TD]
[TD="colspan: 2"]
clip_image001.png

<tbody>
</tbody>
[/TD]
[TD]Service Line[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]Grade[/TD]
[TD](All)[/TD]
[TD]Primary Skills[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]Status[/TD]
[TD](All)[/TD]
[TD]Grade[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]Service Line[/TD]
[TD](All)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Bench Category[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]Primary L2[/TD]
[TD](All)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]GE Previous Account[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]SO Type (SFDC)[/TD]
[TD](All)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Billing/Non-Billing[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]GE/NON-GE[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]Sum of #Pending[/TD]
[TD]Total[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Availability Status[/TD]
[TD](All)[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]833[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Count of Employee ID[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Total[/TD]
[TD="align: right"]614[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 531"]
<tbody></tbody>[/TABLE]



Question;There are common fields like location ,grade,service line
If i apply filters to table one it should automatically apply to other table also.

I have written a code for that(considers only grade field for example).It applies filters in another table but not working when multiple items are selected in filter.
PLease tell me how to apply filter to another table even when multiple tables are selected ?

Sub Button39_Click()
Dim sh As Worksheet
Set sh = Worksheets("Sheet1")
Dim pvt1 As PivotTable
Set pvt1 = sh.PivotTables("pivotinfo1")


Dim pvt2 As PivotTable
Set pvt2 = sh.PivotTables("pivotinfo2")
Dim pvf1 As PivotField
Set pvf1 = pvt1.PivotFields("Grade")
Dim x As Variant


Dim pvf2 As PivotField
Set pvf2 = pvt2.PivotFields("Grade")
Dim pitm1 As PivotItem
Dim pitm2 As PivotItem


For Each pitm1 In pvf1.PivotItems
For Each pitm2 In pvf2.PivotItems
If (pitm1.Visible = True) And (pitm2.Name = pitm1.Name) Then
MsgBox pitm1.Name
x = pitm1.Name
Range("k1").Select
Range("k1") = x
ActiveSheet.PivotTables("Pivotinfo2").PivotFields("Grade").CurrentPage = Range("k1").Text
End If
Next pitm2
Next pitm1
End Sub
 
Last edited:

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top