How do I embed a macro in a cell so it filters the data on another sheet?

[aia8998]

I have a workbook with 2 spreadsheets: Summary and (raw Data)

Summary is a cross tabbed table basically.

soexcel question.png
Self-Assessed Agency-Assessed
1 day 15 (B2) 13
2 days 26 56
3 days 14 16 (C4)
4 days 89 58

When I click on cell B2 of the Summary sheet, I would like the screen to jump to the (raw data) sheet, and set the filter to "1 day" and "Self-assessed". Likewise, if i were to click on cell C4, I would like the screen to jump to the (raw data) sheet, and set the filter to "3 days" and "Agency-assessed".

Can someone tell me the macro and explain it to me so I can apply it to my whole summary sheet?

I appreciate any and all help.

[Richard Buttrey]

Hi,

You can capture the cell selection event by using the WorkSheet_SelectionChange event.
So right click on the tab name to open up this event procedure - it happens to be the default procedure for sheet events.

In there add code like

If Not Intersect(Target, Range("B2")) Is Nothing Then
   Sheet2.Activate
   Range("MyFilterCell") = "1 day"
End If

which assumes that your Raw Data sheet has the VBA codename Sheet2
But just change this as necessary.

You could substitute
Sheets("Raw Data").Activate

but it's generally not good practice to code with sheet tab names since users can easily change them. Code is more robust when you use the VBA sheet code name.

[aia8998]

Thank you so much for the prompt reply!

Nothing is happening so I must be missing something. I am not that skilled in VB so please forgive me.

I right clicked on the tab name on the bottom, and pasted in the code. I am not sure where I should be looking to confirm that the WorkSheet_SelectionChange event is activiated. Where do I confirm that?

[aia8998]

I got it to work with some tinkering around. Thank you very much!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.