Selection change macro

**alanb1976** · 08-17-2011, 11:12 AM

Hey all
I'm trying to write a macro that will run an advanced filter when the user changes the value of a drop down list. I've got the following but it is not working. I'm new to writing scripts so please let me know what I am doing wrong. The advanced filter part should be working as that was a recorded macro from doing the filter which was successful.
Thanks
Alan

Please Login or Register  to view this content.

**Colin Legg** · 08-17-2011, 11:25 AM

Hi Alan,

Please can you edit your post and add code tags around the code? Explained under point #3 here.

Once done, I'll explain what's going wrong with your code.

**TMS** · 08-17-2011, 11:28 AM

Please re-read the forum rules and apply CODE tags to your code sample.

However, you probably need Change rather than SelectionChange.

The Target in SelectionChange is the cell you have moved to, not the cell you have changed.

Regards

**alanb1976** · 08-17-2011, 11:35 AM

Apologies.... edited.

The cell that gets changed is B2. I wasn't sure on how to specify that as there are numerous times that 'range' gets mentioned

**Colin Legg** · 08-17-2011, 11:37 AM

Thanks for adding code tags.

As TM Shucks mentioned, you're trapping the wrong event handler. However, there may also be an issue with the ranges because they are unqualified in the code. Please can you attach your workbook to the thread, or clarify exactly which sheet the CriteriaRange and CopyToRange sit on, and which Sheet contains the data validation Dropdown list?

**alanb1976** · 08-17-2011, 11:52 AM

Thanks.
I have attached the workbook but it's still a mess and had to delete the data due to it containing sensitive company info.
As you can see, there is a 'test' sheet where I want the user to be able to select their name reference from the drop down list (B2). Once done I want it to automatically update filters to display all actions associated with them. At the moment I was just testing with the 'General Actions'. So, from 'test' they would select their name and it would update the 'test' sheet with info from the 'General Actions' sheet that corresponds to the "Owner" column. I hope I am explaining properly.
I am basically looking for a statement like 'if B2 changes, run the following advanced filter'.
Thanks for the help.
Alan

**Colin Legg** · 08-17-2011, 12:12 PM

Okay, the code at the moment is sitting in module1, which is the wrong place. It needs to sit in the Sheet1 module. Also, you only want the advanced filter to be called when B2 is changed, not other cells.

So, your revised code (in Sheet1) would be:

Please Login or Register  to view this content.

**alanb1976** · 08-18-2011, 08:48 AM

Thanks. That was perfect!
Just 1 follow on question if you don't mind. Is there a way of updating a cell shown from the filtered results and having it update the related cell in the other worksheet automatically?
Thanks again
alan