Expected End With Compile Error when trying to run VBA for two Pivot tables

[MRN227]

I'm looking for someone to help me with the below VBA. With one Pivot table on the code the macro runs perfect, but when I add a second table to the code I get a "Expected End With" error:

Sub TestPivot2()
'TestPivot Macro

Dim dt As String
dt = Sheets("Calendar View").Range("valSelEmployee").Value

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee Name")
With ActiveSheet.PivotTables("PivotTable2").PivotFields("List of Employees")

.ClearAllFilters
.PivotFilters.Add Type:=xlCaptionEquals, Value1:=dt
End With

End Sub

 

[KKaren]

It seems that you have opened 2 With blocks, but you have closed only one of them.
Just add another End With and it should work.

 

[Domenic]

Also, your statements within both your With statements will only apply to the inner With statement. Instead, try...

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee Name")
    .ClearAllFilters
    .PivotFilters.Add Type:=xlCaptionEquals, Value1:=dt
End With


With ActiveSheet.PivotTables("PivotTable2").PivotFields("List of Employees")
    .ClearAllFilters
    .PivotFilters.Add Type:=xlCaptionEquals, Value1:=dt
End With

Hope this helps!

 

[Fluff]

Hi & welcome to MrExcel.
You need to do them separately like

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee Name")
   .ClearAllFilters
   .PivotFilters.Add Type:=xlCaptionEquals, value1:=dt
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("List of Employees")
   .ClearAllFilters
   .PivotFilters.Add Type:=xlCaptionEquals, value1:=dt
End With

 

[MRN227]

This is my first time posting here and Im totally new to VBA. Thank you all for the help this works great and my HR is very happy. Thank you so much!!

 

[Fluff]

Glad we could help & thanks for the feedback