Activate this worksheet

[cmb80]

Hi all, I have 5 worksheets all the similar with the same worksheet VBA code.

I need the bit of code to activate this worksheet before a macro is run.

Say for instance sheet 3 is the active one, but the code running on sheet 4 has triggered the macro to run - I need to insert something into sheet 4 code to activate that sheet (instead of sheet 3).

Something like this.worksheet = activate ???

[Norie]

Why do you need to activate Sheet3?

Can't you reference Sheet3 in the code?

[cmb80]

I'm using sheet 3 as the example sheet that was active (selected) at that moment in time.

I'm basically saying is there a way of having code as below, instead of having to change the sheet name for each worksheet (Set ws = sheet1, set ws = sheet 2 etc etc).

Please Login or Register  to view this content.

I quite often create more worksheets by copying existing ones, this means having to change the sheet number in the VBA code each time - if I forget, the code is referencing the wrong worksheet

[Norie]

I'm confused, which sheet(s) do you want this code to run on?

Also, where is the code triggered and where is it located?

[cmb80]

The code is built into each worksheet Private Sub Worksheet_Calculate()

Each worksheet is running its on code. The macro is triggered if cell A1 > 10

So say for instance I have sheet 3 active, and cell A1 = 8 the code should not run. BUT on sheet 4, A1 = 12 - it will call for the macro to run. But the macro will run on sheet 3 (as its the active one). So I am saying I need some code that will activate sheet 4 before running the macro - but I want to do this without specifically typing sheet 4 in the coding (for reasons mentioned above).

There must be a bit of code that will activate a worksheet without having to reference it in Sheet 4, sheet 3 or whatever terms??

[cmb80]

The code is built into each worksheet Private Sub Worksheet_Calculate()

Each worksheet is running its on code. The macro is triggered if cell A1 > 10

So say for instance I have sheet 3 active, and cell A1 = 8 the code should not run. BUT on sheet 4, A1 = 12 - it will call for the macro to run. But the macro will run on sheet 3 (as its the active one). So I am saying I need some code that will activate sheet 4 before running the macro - but I want to do this without specifically typing sheet 4 in the coding (for reasons mentioned above).

There must be a bit of code that will activate a worksheet without having to reference it in Sheet 4, sheet 3 or whatever terms??

[Norie]

Is the code for each worksheet the same?

If it is then you might want to look at the SheetCalculate workbook level event.

Please Login or Register  to view this content.

As you can see it's passed a parameter, Sh, which is the sheet that has been calculated.

[cmb80]

Yes the code is the same, ok will try that and let you know

[cmb80]

Unfortunately this doesn't work, the code doesn't run at all

[Norie]

Are you sure?

Can you post what you tried?

[cmb80]

Sure

Please Login or Register  to view this content.

I then made A1 = (a cell on another sheet). I changed this cell to 12 - and nothing happened (so macro1 wasn't called and the other sheet wasn't selected)

[Norie]

Again a little confused.

Is Macro1 the macro you want to run on each sheet?

Can you post the code for it?

[cmb80]

Yes Macro 1 is the code to run. It is below but not really relevant as the above code doesn't even get to that stage

Please Login or Register  to view this content.

[Norie]

Where did you put the code from post #11?


It should go in the ThisWorkbook module and should look something like this.

Please Login or Register  to view this content.

[cmb80]

AH ok, I was putting it on the sheet code. Will try again