VBA Run a Macro while staying on the same sheet

[Dimitriy]

Hey Everybody,

A quick questions here. I have a main macro that uses the "Call" function to run other macros located in various sheets. When I run the main macro I want to be able to stay on the same sheet(where its button is located), as opposed to jumping through all sheets where the macros are being executed.

I have tried using Application.ScreenUpdating function, but it does not seem to work.

Do you guys know how to get this working?

Thanks!

[YasserKhalil]

Try this

Sheets(1).Range("A1").Select
Application.ScreenUpdating=False

'Your macro

Application.ScreenUpdating=True

[Artik]

You are probably using the Select method in the macro quite unnecessarily. If you can not change the code, use this code structure in your main macro:Sub MainMacro()
Dim ActWks As Worksheet

Set ActWks = ActiveSheet
Application.ScreenUpdating = False

'Your macro

ActWks.Activate
Application.ScreenUpdating = True

Set ActWks = Nothing
End Sub

Artik

[Dimitriy]

Unfortunately that didn't work...not sure what I am doing wrong. Here is the code I am using. I am selecting different sheets because the macros 2-6 are written specific to each sheet:



Sub Macro1()

Sheets("1").Select
Range("$B$3").Select

Application.ScreenUpdating = False

Sheets("2").Select
Call Macro2

Sheets("3").Select
Call Macro3

Sheets("4").Select
Call Macro4

Sheets("5").Select
Call Macro5

Sheets("6").Select
Call Macro6

Application.ScreenUpdating = True

End Sub

[geekgirlau]

As Artik suggested, all your macros probably need revision - it is rare that you actually need to select a sheet in order to perform some changes on it. However as you haven't posted that code we can't make any suggestions.

Try the following:


Sub Macro1()
Sheets("1").Select
Range("$B$3").Select

Application.ScreenUpdating = False

Sheets("2").Select
Call Macro2

Sheets("3").Select
Call Macro3

Sheets("4").Select
Call Macro4

Sheets("5").Select
Call Macro5

Sheets("6").Select
Call Macro6

Sheets("1").Select
Application.ScreenUpdating = True
End Sub


I would also suggest that you try naming your macros (and your sheets for that matter) something a little more intuitive than "Macro1".

[Dimitriy]

Thanks for your replies. The code above was just for illustrative purposes, therefore names like sheet 1 and macro 2.

Can you guys provide examples of how I can perform changes to a sheet without actually selecting it.

Thanks!

[geekgirlau]

There are only a handful of procedures where you need to select the sheet first (from memory setting freeze panes is one example). Otherwise, you just specify the sheet and range you want to impact:


Sheets("1").Range("A1").Formula="1"

[Artik]

Maybe differently. You show us code and we'll tell how to change it.

Artik