Curious: One works, the other doesn't ---- why????

spydey

Active Member
Joined
Sep 19, 2017
Messages
296
I have a workbook with multiple sheets.

I have some simple code that I thought would work, but for some reason it doesn't.

Here is the code in a module:

Code:
Sub GoHome()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook

For Each ws In wb.Worksheets
     ws.Range("A1").Select

Next ws

End Sub
I run it and it doesn't work.

However, if I add:

ws.Activate

Prior to ws.Range("A1").Select, then it works perfectly.

Why do I need to activate each worksheet prior to selecting that worksheet's A1 cell?

Thanks for the clarification!!

-Spydey
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,055
Office Version
365
Platform
Windows
Open up a workbook with say 2 sheets. From 'Sheet1' select A1 of 'Sheet2'. You cant unless you activate 'Sheet2' first. Hence why you cant in code either. You rarely need to select anyway.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,577
Office Version
365
Platform
Windows
You can only select a cell on the active sheet.
That said it's very rare that you need to select things.
 

spydey

Active Member
Joined
Sep 19, 2017
Messages
296
Open up a workbook with say 2 sheets. From 'Sheet1' select A1 of 'Sheet2'. You cant unless you activate 'Sheet2' first. Hence why you cant in code either. You rarely need to select anyway.

Thank you @steve the fish

I figured that was the case but I could have sworn I have many examples of code where I am on one sheet and doing things to another sheet in the same workbook, but never activated the 2nd sheet.

I could be wrong .....

Thanks again!!

-Spydey
 

spydey

Active Member
Joined
Sep 19, 2017
Messages
296
You can only select a cell on the active sheet.
That said it's very rare that you need to select things.
@Fluff

So my ultimate goal is to bring the view up to Cell A1 on each sheet, rather than some random cell.

Would it be more appropriate to Activate cell A1 rather than select cell A1?

-Spydey
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,577
Office Version
365
Platform
Windows
To move A1 to the top of the screen you will need to select the sheet & activate the cell
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,055
Office Version
365
Platform
Windows
I often use goto

Code:
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range

Set wb = ThisWorkbook

For Each ws In wb.Worksheets
    Set rng = ws.Range("A1")
    Application.Goto rng, True
Next ws
 

Watch MrExcel Video

Forum statistics

Threads
1,095,400
Messages
5,444,253
Members
405,278
Latest member
Rashford

This Week's Hot Topics

Top