asked on

Autorun autofilter upon opening a worksheet

Hi, (I might have figured this out a long time ago, albeit slowly, but I was never very good at VBA) :(

I have the same advanced filter on a few worksheets (in same workbook) and I need the auto-filter to immediately auto-run when the worksheet is opened. That is, when a worksheet that has an advanced filter set in it.

Can you help please.

BTW: If you want a copy of the workbook please yell & I'll have to dummy one up for you (as it's confidential data).

Jacques Geday

could you post the workbook so we make sure it is done correctly.
But offhand whatever code to prepare for autofilter if you want it to be activated when you open the workbook then you should make sure the code is in this Sub

Private Sub Workbook_Open()

End Sub

Open in new window

gowlfow

kazmdav

ASKER

Sure, no worries! A dummied up sample workbook is attached. Example use: In 'Foundation Skills (1)' in Col A, user selects from drop down. Foundation Skills (2) has the advanced filter (ie; filter out the rows that had 'No' selected). It's this filter that I want to occur when this worksheet is opened.

Same concept for Specialisation (1) & Specialisation (2) worksheets. Thus when Specialistation (2) is opened I want the advanced filter to occur too.

Hope this helps!
Sample-workbook.xls

Jacques Geday

ok lets take it step by step.

First I see some errors in sheet Foundation Skills (2) Row 3, 4, 5, 6 is it safe to delete these rows or something else should go there ?? as they point to Foundation Skills (1) #REF! ref means that the reference to the mentioned sheet is missing.

gowflow

kazmdav

ASKER

Yes no problems, safe to delete them (it's as a result of me removing heaps of confidential data from Foundation skills (1)).

cheers!

Jacques Geday

Furthermore I noticed that maybe in your sample workbook you made a crucial mistake of design where you included the choices in the sheets (2) like if the user go on sheet 2 and select a No then it will disappear from that sheet and will still show no value in the initial sheet 1

I think your aim is to have the user select in sheet 1 and have the sheets 2 for lookup and have it filtered excluding No Am I right ???
gowflow

Jacques Geday

Also what about the option of No but interested ? you want to be filtered out or kept showing ??
gowflow

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kazmdav

ASKER

Perfect! Thank you gowflow. Exactly what I wanted & I don't believe I require anything further.

I will put the code into production workbook tomorrow. After the update & successful test in prod, I will return here to close the question.

Sorry for no response; was on way home from work.

Cheers!

Jacques Geday

No problem take your time and pls feel free to post here any link to any question you may need help with.
Rgds/gowflow

kazmdav

ASKER

All good. Thank you. :)