Run a Macro on Several other Worksheets without leaving the current one

[hazza147]

Hi,

I have the below code:

Please Login or Register  to view this content.

The idea is that it goes through each worksheet and runs a macro on that worksheet, however it does not run on every worksheet only on SHEET4. The only way I can get it to run on every worksheet is to use the following code:

Please Login or Register  to view this content.

With ws.Activate on line 7 being the key part. However this results in screen flickering as it goes through every worksheet and this isn't ideal! I have tried a variety of different methods including Application.ScreenUpdating etc however with no luck.

Anybody got any ideas??

Many thanks.

Harry

[DonkeyOte]

Harry, the key is the code you're invoking and how it's constructed.

I suspect you should be passing a Sheet reference to that routine (ie via a variable), subsequently applying With Statements within that routine so as to ensure the code is applied to the appropriate object.

[hazza147]

Here is a little snippet of the code for Module11.PricelistChange:

Please Login or Register  to view this content.

Based on the above can you see anything that would mean only the .Activate method would allow the update of each sheet?

Cheers,
Harry

[DonkeyOte]

Without wanting to get too involved I will just elaborate on my earlier point by means of example...

Please Login or Register  to view this content.

and in Module11

Please Login or Register  to view this content.