VBA or Macro to auto update a table

[d3ell]

Hi,

I have a table that is populated via from data in other sheets. Each Cell basically has an IF syntax and if true that row of the large table has data in.

As this table is 254 rows in total i may only have data in 3 or 4 rows, what i can do is filter a column and uncheck (blanks) and then i just get the 3 or 4 rows that i want.

I'm hoping to have the data filtered automatically on Worksheet change.

I have had a bit of a play around with this and looked on the web, but cant find anything that works.

All i want it to do is when the worksheet is selected from another sheet that the filter is unapplied and then applied again.

I have tried inserting the below, which i found and edited to suit my data but when i put it in the visual basic when i press the play button i get a pop up saying Macros, which i giving me the option to create a macro, but not use the code i have just put in.

Please Login or Register  to view this content.

I'm clearly doing something wrong? any ideas?

Also, if the above would work, could i replace the range somehow for the table name?

[Bernie Deitrick]

1) Copy this code.
2) Open the VBE (press Alt-F11)
3) Select your workbook in the project explorer
4) Double-click the ThisWorkbook object
5) Paste the code into the window that appears.
6) Save the file as a macro-enabled .xlsm file.

Please Login or Register  to view this content.

And to use a table name, change to

Please Login or Register  to view this content.

[Eastw00d]

you must put this code in the worksheet module, to open with a.o. right-click on the worksheet tab; View Code, not a regular module.
and I think you want to use "Worksheet_SelectionChange"

Cheers
Erwin

[d3ell]

Hi

thanks for your help.

When i try and run this i'm getting a pop up - in the debug mode the line showing Sh.ShowAllData 'Better way is highlighted yellow if i try and press play again i get the following pop up...

Run-time error '10041:

ShowAllData method of worksheet class failed


My code is below and it is in the "ThisWorkbook" object

Please Login or Register  to view this content.

[Bernie Deitrick]

I should have thrown in:

Please Login or Register  to view this content.

because if the sheet or table is not filtered, removing filters will create an error.

Please Login or Register  to view this content.

[d3ell]

This Seems to work Perfectly! - thanks so much!!

[d3ell]

One other quick question, if i have multiple sheets where i require this, is there a way to enter multiple sheets into the code? I have tried using a semi colon but it didn't like that!

otherwise would i just have to repeat the whole sub again for each sheet?

Thanks again

[Bernie Deitrick]

No - just check the names of all the sheets you want to use - like this, if the ranges could be different but always include A4: - And somehow I left off the C of Criteria1!

Please Login or Register  to view this content.

[d3ell]

Hi thanks,

this worked again, my next issue, is that i have a sheet with 2 tables on that i would like to do the same. - they have identical headers, but are underneath each other. I have tried the following things to no avail. the sheet where i have 2 tables is called "Code Sheet" - FYI - the 1st sub all works correctly, but when i insert code for the 2nd sub i get quite a lot of errors!?

Sorry, but if i can conquer this part i think i will be done!

Please Login or Register  to view this content.

[Bernie Deitrick]

Well, you can't do that. You can only apply one filter per sheet, and you can only have one event per event. What you could do is

1) use this - your macros combined, but only one filter is applied to "Code Sheet" dependent on where you are in the sheet

Please Login or Register  to view this content.

2) use a a 'helper' column of formulas that spans both tables and allows some space between the tables, and filter on that column

3) use two views of the sheet and switch back and forth between the filters

[d3ell]

Thankyou, knowing that i have now merged the table into one long table and managed to use only one filter to do what i needed to do! all that is working great now.

One other thing i've come across and not sure if this would be resolvable but i have locked all the cells and then protected the sheet. I want users to be able to copy the information from it, but not change it as all the data is basically just formulas. (they will have to copy the cells and then paste values onto another sheet) This also works when i protect the sheet, but i have found that while the sheet is protected that the auto filter vba code doesnt work? - can this be over-ridden in the code? or is there no way for the auto filter to work if the sheet is protected? Ta.

[Bernie Deitrick]

You need to apply the protection using VBA - that allows you to set the UserInterfaceOnly:=True parameter, which allows VBA to make changes to protected sheets, like

Please Login or Register  to view this content.

If you are protecting specific things, then just record a macro doing the protection, and add the UserInterfaceOnly:=True to the end of the code:

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

[d3ell]

Brill!

thanks for all your assistance! Much appreciated!