how to run a macro by selecting a cell

[powdow]

I have a Table of Contents listing about 20 reports. I have about 20 sheets all titled RPT1_Regions, RPT2_Regions, RPT3_Regions etc. Then I have 20 sheets for RPT1_Sites, RPT2_Sites, RPT3_Sites etc. Then I have 20 sheets for RPT1_Subsites etc. I have a macro that when a REgion is selected on RPT1_Regions, it will advancefilter all the RPT#_Region, RPT#_Sites and RPT#_Subsites and just show the region selected. I also have the macro select the RPT1_Sites sheet. Works exactly the way I want it to if RPT1 is selected, how to I do the same thing if the user selects a report other than report 1?
Here are my macros and I've attached an example of the workbook.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Excel 2010

[p24leclerc]

Not really sure what you're doing but try those modifications to your macros:

Please Login or Register  to view this content.

BTW, put this one in the "ThisWorkbook part of your VBAProject and delete the one in RPT1_Regions sheet.

Please Login or Register  to view this content.

Hope it will help you

[powdow]

Thanks, I see it is doing the advanced filtering now whether I start with RPT1 or RPT2. It takes me to RPT2_Subsites, how do I make it take me to the Subsite report of the same report number? In other words, if I go to RPT2_Region and select Region 3, I want to go to RPT2_Sites which will be showing only Region 3.

[p24leclerc]

change your Test macro with this one:

Please Login or Register  to view this content.

[powdow]

Thanks, that worked great. On the Private Sub, how do I limit it to just the RPT_Region sheets? I did move it to ThisWorkbook as you indicated. I originally had it working on just the lstRegions but only on RPT1. I would like it to work on all the RPT_Region reports, but not on the Site or SubSite reports.

Please Login or Register  to view this content.

[p24leclerc]

Hi, I will answer your question for the last time as it is against the rules to keep asking new questions on and on on the same thread. You must open a new thread.

Change the macro to this one:

Please Login or Register  to view this content.

You just add a condition to check if the sheet name where the selection change as occured has the word "Region" in it.

[powdow]

Sorry, I am new to the Forum. This didn't work, I changed it to this and now the advanced filtering doesn't work. I'll try asking the question again.

[p24leclerc]

It should work. I changed nothing else. The macro checks if there is the word "Region" in its name. Make sure this is true. Otherwise, change this word with something constant within all your report names.
Good luck.

[powdow]

Thanks, I did get it to work. Still pops an error when I select the report form Table of Contents, but all the other sheets work as expected. Thank you for your help and your patience with a newbie!