control-pivot-table-filter-via-dropdown-box-vba.

[Kumar Shanmugam]

Hi, The Below thread for filtering Pivot tables based on Combobox drop down values is very useful and informative.
https://chandoo.org/forum/threads/control-pivot-table-filter-via-dropdown-box-vba.32706/
I tried to replicate the final solved code as per my data model. It worked well.But as I creased my filter critieria i am getting an runtime error: Application or object defined error.
To explain the problem in a simple manner. First i would like to show the code which worked well and fine no issues.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>> use code - tags <<<

Sub Dropdown3()

Application.ScreenUpdating = False
Dim vItem As String
Dim zItem As String
Dim xItem As String
Sheets("DB 1").Select //*DB 1 is the Dashboard sheet where the combo box are present"//
Sheets("DB 1").Visible = True
Sheets("Calc").Visible = True  //*Calc is the Pivot table sheet"//
Sheets("Calc").Select
ActiveSheet.PivotTables("ProductFilter").ClearAllFilters
vItem = "[RepoSaleHR].[STATEDESC].&[" & Range("T6").Text & "]"
xItem = "[RepoSaleHR].[STOCKYARDLOCATION].&[" & Range("Y6").Text & "]"
zItem = "[RepoSaleHR].[PRODUCTMODEL].&[" & Range("BM6").Text & "]"
ActiveSheet.PivotTables("ProductFilter").PivotFields("[RepoSaleHR].[STATEDESC].[STATEDESC]").VisibleItemsList = Array(vItem)
ActiveSheet.PivotTables("ProductFilter").PivotFields("[RepoSaleHR].[STOCKYARDLOCATION].[STOCKYARDLOCATION]").VisibleItemsList = Array(xItem)
ActiveSheet.PivotTables("ProductFilter").PivotFields("[RepoSaleHR].[PRODUCTMODEL].[PRODUCTMODEL]").VisibleItemsList = Array(zItem)
Sheets("Calc").Visible = True
Sheets("DB 1").Select
Application.ScreenUpdating = True

End Sub

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Now my requirement of Pivot Table filter increases. There is another pivot table PivotTable13 where I want to apply the same filter based on combobox selection value.

So I replicated the same in Pivot Table 13 but I got runtime error: Application or object defined error.

Sub Dropdown3()

Application.ScreenUpdating = False
Dim vItem As String
Dim zItem As String
Dim xItem As String
Sheets("DB 1").Select //*DB 1 is the Dashboard sheet where the combo box are present"//
Sheets("DB 1").Visible = True
Sheets("Calc").Visible = True  //*Calc is the Pivot table sheet"//
Sheets("Calc").Select
ActiveSheet.PivotTables("ProductFilter").ClearAllFilters
ActiveSheet.PivotTables("PivotTable13").ClearAllFilters
vItem = "[RepoSaleHR].[STATEDESC].&[" & Range("T6").Text & "]"
xItem = "[RepoSaleHR].[STOCKYARDLOCATION].&[" & Range("Y6").Text & "]"
zItem = "[RepoSaleHR].[PRODUCTMODEL].&[" & Range("BM6").Text & "]"
ActiveSheet.PivotTables("ProductFilter").PivotFields("[RepoSaleHR].[STATEDESC].[STATEDESC]").VisibleItemsList = Array(vItem)
ActiveSheet.PivotTables("ProductFilter").PivotFields("[RepoSaleHR].[STOCKYARDLOCATION].[STOCKYARDLOCATION]").VisibleItemsList = Array(xItem)
ActiveSheet.PivotTables("ProductFilter").PivotFields("[RepoSaleHR].[PRODUCTMODEL].[PRODUCTMODEL]").VisibleItemsList = Array(zItem)
ActiveSheet.PivotTables("PivotTable13").PivotFields("[RepoSaleHR].[STATEDESC].[STATEDESC]").VisibleItemsList = Array(vItem)
ActiveSheet.PivotTables("PivotTable13").PivotFields("[RepoSaleHR].[STOCKYARDLOCATION].[STOCKYARDLOCATION]").VisibleItemsList = Array(xItem)
ActiveSheet.PivotTables("PivotTable13").PivotFields("[RepoSaleHR].[PRODUCTMODEL].[PRODUCTMODEL]").VisibleItemsList = Array(zItem)  
Sheets("Calc").Visible = True
Sheets("DB 1").Select
Application.ScreenUpdating = True

End Sub

Above Highlighted one: ActiveSheet.PivotTables("PivotTable16").PivotFields("[RepoSaleHR].[PRODUCTMODEL].[PRODUCTMODEL]").VisibleItemsList = Array(zItem) is where i am getting an error in VBA code.

I dont understsand what is the problem in the above line if the same works with this line: ActiveSheet.PivotTables("ProductFilter").PivotFields("[RepoSaleHR].[PRODUCTMODEL].[PRODUCTMODEL]").VisibleItemsList = Array(zItem).

Please help.