Have VBA code that needs performed in multiple sheets. Also help being sure code is clean

[baby_kay_2003]

I have a VBA that I need to perform in multiple sheets in my workbook. Also would like help being sure code is as cleaned up as possible.
I need the below code performed in 2 of the 4 sheets in my workbook(for now. a third will be added soon). The sheets are Lawton and Sherman. The sheets in workbook not needing this code are Des Monies and Cedar Rapids. I have tried many options I have seen online and cannot seem to get it correct. As you can probably tell I am new to this. lol.

Please Login or Register  to view this content.

Any help would be greatly appreciated!!!

[Pauleyb]

Generally, if you run code in a module it will perform on the ActiveSheet. So, you can write generic code and then run it from each sheet and it should work.
You could also reference specific sheets with something like this:

Please Login or Register  to view this content.

As for cleaning up your code,
- I see a lot of unnecessary selection.autofilter statements.
- In VBA you rarely need to use select or activate. You can use range references (as you did at the start of your code) or variables to keep track of the different cells and ranges. This is considered good coding, since the user does not have their selected cell modified. Thus, my sheet advice above is also not good coding, you should use a sheet variable to switch between sheets, and not actually activate a specific sheet. This is better code (as an example):

Please Login or Register  to view this content.

- A CASE statement may be better than all of the autofiltering you are performing. Just do a loop going down each cell in the table and use a case statement (based on your critieria - e.g. WIRELESS VIDEO BRIDGE) and what actions to then perform. At least, that's the way I would do it, but if your code works, don't modify it just for the sake of having it in a loop. It is fairly clear what you are doing. Always good to add comments, though, in case you or someone else is trying to follow the code at a later date.

[baby_kay_2003]

Thank you for all the pointers! The code does work and does exactly what I need it to do but it seems slow. So I thought cleaning the code may speed it up. Do you think the case statement would make it faster? I understand how case statements work but do not know how to write them. Could you show me a sample of how I should do it? Or point me to a good forum post about writing it?

[Journeyman3000]

There are always lots of way to do stuff in Excel. My apologies if you already know this stuff.

For example, you could put the code into a module, then call the function from each Worksheet_Activate event.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Note that when passing the name of sheet to the function, you can then simplify your code for useability by mulitple sheets. For example:

Please Login or Register  to view this content.

Also, try not to use "Selection". This will slow your code down a lot. Instead you can try setting your range and working within it. For example:

Please Login or Register  to view this content.

Which will of course populate 100 rows in column A with the word "Hello", you can begin to see how simple it becomes

If you get use to working this way, your code with be leaner and much easier to follow.

Also here is a Select Case example. in this example, I'm looking for the value of 1

Please Login or Register  to view this content.

This should result in a message box returning "Bananas"

Hope this helps

[baby_kay_2003]

Thank you so much! I am going to try to rewrite the code with a range. Again thank you for taking the time to answer my question!