Moving sheets when Deactivate updates multiple sheets

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
88
Office Version
  1. 2010
Platform
  1. Windows
Hello,

I wonder if somebody can help me please.

I have a workbook with 6 sheets. I'd like to run a macro (macro1) that updates sheets 2-6 when I move from sheet 1 to one of the other sheets. I've tried to call macro1 with a Private Sub Worksheet_Deactivate() code on sheet1 but (as expected I guess :)) I always end up on sheet 6 (the last sheet updated by macro 1) rather than the sheet I select to close sheet 1.

Is there a way I can get around this - is there VBA code for "go to selected sheet" I can put at the end of macro 1 or the Deactivate code?

Any help much appreciated.

Thank you,
Iain
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,616
Office Version
  1. 365
Platform
  1. Windows
It sounds as though you are selecting sheets within the code, which is rarely necessary.
Can you post the code you are using?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,041
Office Version
  1. 365
Platform
  1. Windows
Put this at the beginning of your code to "capture" the sheet you just moved to dynamically:
VBA Code:
'   Capture new active sheet
    Dim ws As Worksheet
    Set ws = ActiveSheet
Then if you want to go back to that sheet at the end of your code, just use:
VBA Code:
    ws.Activate
 
Solution

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
88
Office Version
  1. 2010
Platform
  1. Windows
Hi Fluff,

This worked a treat. Thank you very much for replying

Iain
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,616
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,147,568
Messages
5,741,882
Members
423,693
Latest member
Excelquestion35

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top