Help with a VBA code for filter multiple pivot tables across multiple sheets by a value in a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-3073380%22%20slang%3D%22en-US%22%3EHelp%20with%20a%20VBA%20code%20for%20filter%20multiple%20pivot%20tables%20across%20multiple%20sheets%20by%20a%20value%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073380%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20to%20VBA%20and%20still%20getting%20my%20head%20around%20the%20language.%20I%20have%20tried%20searching%20the%20internet%20but%20I%20am%20not%20able%20to%20find%20a%20macro%20that%20will%20help%20my%20situation.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EI%20am%20trying%20to%20build%20a%20dashboard%20in%20excel.%20The%20summary%20of%20the%20data%20will%20be%20showcased%20across%20multiple%20sheets%20and%20therefore%20I%20require%20a%20macro%20to%20be%20able%20to%20apply%20filter%20across%20multiple%20pivot%20tables.%20To%20simply%20explain%20in%20an%20example%2C%20I%20have%20a%20data%20validation%20list%20in%20Sheet%201%20%26amp%3B%202%20in%20cell%20A1.%20At%20the%20moment%20using%20this%20%3CA%20href%3D%22https%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fall%2Faltering-multiple-sheets-with-data-validation%2Fa2333cb2-1d54-4d85-b9d2-225d31ed4749%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EMacro%26nbsp%3B%3C%2FA%3E%26nbsp%3BI%20have%20been%20able%20to%20ensure%20that%20regardless%20whether%20the%20value%20is%20selected%20in%20Sheet%201%20or%202%20both%20A1%20cells%20will%20match%20each%20other.%20However%2C%20here%20is%20where%20I%20get%20stuck%2C%20I%20have%20tried%20to%20use%20worksheet%20even%20handler%20'Change'%20in%20Sheet%201%20A1%20but%20then%20when%20I%20go%20to%20Sheet%202%20A1%20and%20select%20the%20a%20value%20from%20the%20drop%20down%20list%20it%20fails.%20Although%20the%20cell%20value%20in%20Sheet1%20A1%20changes%20the%20macro%20in%20Sheet1%20does%20not%20trigger.%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3ESo%20in%20summary%2C%26nbsp%3B%3CSPAN%3Eis%20it%20possible%20to%20for%20a%20macro%20to%20be%20executed%20when%20a%20cell%20in%20an%20inactive%20sheet%20changes%20within%20the%20same%20workbook%20changes%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EPlease%20advise%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3073380%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hi,

 

I am new to VBA and still getting my head around the language. I have tried searching the internet but I am not able to find a macro that will help my situation. 

 

I am trying to build a dashboard in excel. The summary of the data will be showcased across multiple sheets and therefore I require a macro to be able to apply filter across multiple pivot tables. To simply explain in an example, I have a data validation list in Sheet 1 & 2 in cell A1. At the moment using this Macro  I have been able to ensure that regardless whether the value is selected in Sheet 1 or 2 both A1 cells will match each other. However, here is where I get stuck, I have tried to use worksheet even handler 'Change' in Sheet 1 A1 but then when I go to Sheet 2 A1 and select the a value from the drop down list it fails. Although the cell value in Sheet1 A1 changes the macro in Sheet1 does not trigger.

 

So in summary, is it possible to for a macro to be executed when a cell in an inactive sheet changes within the same workbook changes?

 

Please advise

0 Replies