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"]
<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
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"]
![]() | ||
<tbody>
</tbody>
[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: