trouble with loop procedure

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Can someone please tell me why this does not select A1 in all the sheets of
a workbook?
(I know there must be something wrong with the way the loop is designed as
it only does the active worksheet.)

Dim ws As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Range("A1").Select
Next
Application.ScreenUpdating = true
End Sub

Also, I notice that when I hover the mouse over the
"Application.ScreenUpdating" part whether it is =true or =false, the yellow
box that appears always says ... = true.
I thought this would show false if false and true if true. So is that not
working as well??
Thank you,
Rob
 
Rob,

When you loop through the worksheets in the For Each loop, the
active sheet doesn't change. It remains the same throughout the
loop unless you specifically activate another sheet. You could
change your code to

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Range("A1").Select
Next

or, better,

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


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Thanks Chip.
The first one works great with Dim ws as Worksheet
Can't get the second (better) one to work though.....

Sub test()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Range("A1").Select
Next
End Sub

Says, Application defined or Object defined error
Rob
 
I'm betting that Chip made a typo in the "even better" version:

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Application.Goto ws.Range("a1"), scroll:=True
Next
 
Thank you both again.....but I'm staying right outa this one.

Rob

Chip Pearson said:
Dave,

If "typo" means "didn't test" then, yes, I made a typo.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
 
Back
Top