Activate a sheet without using "Activate"

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
155
I have read that it is best not to use .Activate whenever possible. But I can't figure out how to do something without it.

In my workbook, one thing it will do is copy sheets into a new workbook. Once it copies the sheets, I want the code to select the first workbook in the sheet, so that when the user opens that workbook later, the first one will be the one it opens on. I can do that with this code:

Code:
myWB.Sheets(1).Activate
After this, my code closes the workbook (myWB) and then I activate the workbook with the code by:

Code:
ThisWorkbook.Activate
Is there a way to do the same thing without using .Activate?

Another reason that I want to avoid it is that even though I have ScreenUpdating set to False, when the Activate lines execute, I see the screen flash for a second.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,403
Office Version
365, 2010
Platform
Windows, Mobile
Code:
Application.Goto myWB.Sheets(1).Cells(1, 1)
But you will still get some screen flash.
 

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
155
Thanks. Is there no way to avoid the screen flash? I don't understand what is causing it.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,738
Thanks. Is there no way to avoid the screen flash? I don't understand what is causing it.
Don't notice any flash when activating one sheet. You could try activating that sheet when you open the workbook with a workbook_open event like this:
Code:
Private Sub Workbook_Open()
Sheets(1).Activate
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,700
Messages
5,446,016
Members
405,377
Latest member
joaofigu78

This Week's Hot Topics

Top