Update multiple pivot tables using a combo box in a different sheet

Hi
I am trying to update 2 pivot tables in two different worksheets with a combo box thats in a different sheet. These are all in the same workbook.

I referenced this instructional video http://www.datapigtechnologies.com/f...s/pivot10.html which shows how to refresh multiple pivots with the combo drop down in the 'SAME' sheet.

I have used this as a base and created the following code, but I get a run time error 'runtime error 1004 unable to get pivot tables property of worksheet class'. (Error highlighted in red)

Can someone look at this code and please correct it? Note that my sheet names are correct and I'm using the correct pivot table names in my code. I recorded a macro to double check all the names. Please help!!

Please Login or Register  to view this content.

Also note that both the data sets have the same 'bands' and Cell 'AB5' has an INDEX formula that is driven by the Combo box.
I cannot use slicers as they are different data sets and I cannot combine them.


Appreciate your assistance.
Cheers!