I run a macro in Excel that starts on tab 1, then goes to tab 2,3,4,5,etc.
and when it is finished, it returns to tab 1. When the macro runs, each of
the screens (tabs) flash by as the macro works on that tabs data and then
returns back to the beginning (tab 1). How can I keep the screen (tab 1)
active as the macro goes to each tab since it will end up at tab 1 at the end
of the macro?

Application.ScreenUpdating = False will help but if you have a lot of select
and activate in your code, it probably won't completely eliminate it. Also
don't forget to use Application.ScreenUpdating = True at the end of your
program.

"RandyPerry" wrote:

> I run a macro in Excel that starts on tab 1, then goes to tab 2,3,4,5,etc.
> and when it is finished, it returns to tab 1. When the macro runs, each of
> the screens (tabs) flash by as the macro works on that tabs data and then
> returns back to the beginning (tab 1). How can I keep the screen (tab 1)
> active as the macro goes to each tab since it will end up at tab 1 at the end
> of the macro?

You can do a lot of things to a sheet or sheets without selecting the sheet or
sheets.

If you post your code, someone may be able to clean it up.

As an alternative the screenupdating can be turned off as JLGWhiz points out.


Gord Dibben MS Excel MVP

On Thu, 7 Feb 2008 16:26:01 -0800, RandyPerry
<(E-Mail Removed)> wrote:

>I run a macro in Excel that starts on tab 1, then goes to tab 2,3,4,5,etc.
>and when it is finished, it returns to tab 1. When the macro runs, each of
>the screens (tabs) flash by as the macro works on that tabs data and then
>returns back to the beginning (tab 1). How can I keep the screen (tab 1)
>active as the macro goes to each tab since it will end up at tab 1 at the end
>of the macro?

Thanks, JLGWhiz. It didn't stop the flashing as it worked on each sheet
before returning back to sheet 1.

Let me rephrase the question: Can I run a macro from sheet 1 without having
to activate each sheet I want the maco to work on, thus keeping sheet 1
active?

I use a button control on sheet 1 to run the macro. Sheet 1 is a summary
sheet that I use to operate several other macros that will take me to
individual sheets for local data work.

Here is some code that I'm using now:

Worksheets("W. Columbus Ave").Activate
Range("A6:A17").Select
Selection.ClearContents
Range("A6").Select
' ClearGenoa Macro
Worksheets("N. Genoa Ct").Activate
Range("A6:A11").Select
Selection.ClearContents
Range("A6").Select
' ClearFerdinand Macro
Worksheets("N. Ferdinand Ct").Activate
Range("A6:A12").Select
Selection.ClearContents
Range("A6").Select

This continues for 10 sheets and then makes sheet 1 active and selects cell
A2 to finish.

Regards
Randy

"JLGWhiz" wrote:

> Application.ScreenUpdating = False will help but if you have a lot of select
> and activate in your code, it probably won't completely eliminate it. Also
> don't forget to use Application.ScreenUpdating = True at the end of your
> program.
>
> "RandyPerry" wrote:
>
> > I run a macro in Excel that starts on tab 1, then goes to tab 2,3,4,5,etc.
> > and when it is finished, it returns to tab 1. When the macro runs, each of
> > the screens (tabs) flash by as the macro works on that tabs data and then
> > returns back to the beginning (tab 1). How can I keep the screen (tab 1)
> > active as the macro goes to each tab since it will end up at tab 1 at the end
> > of the macro?

After getting the screenupdating=false/true where they needed to be, it
works! Thanks JLGWhiz,
Randy


"JLGWhiz" wrote:

> Application.ScreenUpdating = False will help but if you have a lot of select
> and activate in your code, it probably won't completely eliminate it. Also
> don't forget to use Application.ScreenUpdating = True at the end of your
> program.
>
> "RandyPerry" wrote:
>
> > I run a macro in Excel that starts on tab 1, then goes to tab 2,3,4,5,etc.
> > and when it is finished, it returns to tab 1. When the macro runs, each of
> > the screens (tabs) flash by as the macro works on that tabs data and then
> > returns back to the beginning (tab 1). How can I keep the screen (tab 1)
> > active as the macro goes to each tab since it will end up at tab 1 at the end
> > of the macro?

Randy, I took the code snippet you posted and "cleaned" it up to eliminate
the selects and activates.

Set Wks1 = Worksheets("W. Columbus Ave")
Wks1.Range("A6:A17").ClearContents
Set Wks2 = Worksheets("N. Genoa Ct")
WKs2.Range("A6:A11").ClearContents
Set Wks3 = Worksheets("N. Ferdinand Ct")
Wks3.Range("A6:A12").ClearContents

Using this style of code writing allows the code to execute without the
flicker and flash effect. The only thing is that you have to remember to
qualify all of your range objects with the sheet reference so it know where
to execute. Otherwise it assumes you want the active sheet to be the target.
You should practice up on this style if you will be doing a lot of code
writing, especially if you should decide to write a long program involving
more than one workbook and more than one sheet.
There are other little gimmicks that stop the flickering, like setting
calculation to manual. But the main thing is to get away from the select and
activate as much as possible. Sometimes, I still find it convenient to use
the activate method, but selection is no longer in my code.

"RandyPerry" wrote:

> After getting the screenupdating=false/true where they needed to be, it
> works! Thanks JLGWhiz,
> Randy
>
>
> "JLGWhiz" wrote:
>
> > Application.ScreenUpdating = False will help but if you have a lot of select
> > and activate in your code, it probably won't completely eliminate it. Also
> > don't forget to use Application.ScreenUpdating = True at the end of your
> > program.
> >
> > "RandyPerry" wrote:
> >
> > > I run a macro in Excel that starts on tab 1, then goes to tab 2,3,4,5,etc.
> > > and when it is finished, it returns to tab 1. When the macro runs, each of
> > > the screens (tabs) flash by as the macro works on that tabs data and then
> > > returns back to the beginning (tab 1). How can I keep the screen (tab 1)
> > > active as the macro goes to each tab since it will end up at tab 1 at the end
> > > of the macro?