Cells.Select - Why not in a module?
 

Hi All,

Can anyone explain why the following code does not work in a module:


Sub SelAll()

For Each Sht In ActiveWorkbook.Worksheets

Sht.Cells.Select

Next

End Sub



Whereas, the following code works fine in a worksheet code:

Sub SelSht()

Cells.Select

End Sub


I am guessing it is related to scope, but I cannot see why from my
reading of the excel help files.

Thanks in advance,

Alan.

Cells.Select - Why not in a module?
 

Alan

As far as I know you can not select on a sheet that is not active


try

Sub SelAll()
Dim sHt As Worksheet
For Each sHt In ActiveWorkbook.Worksheets
sHt.Activate
sHt.Cells.Select
Next
End Su

--
Message posted from http://www.ExcelForum.com

Cells.Select - Why not in a module?
 

You cannot select cells in other than the active sheet.
You must first select the sheet before selecting the
cells. For example, this works:

Sub SelAll()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
Sht.Select
Sht.Cells.Select
Next
End Sub

Your second code example works because it references the
active sheet. The cells method, when not qualified,
refers to the active sheet; i.e. the
statement "Cells.Select" by default refers to the cells of
the active sheet.

Regards,
Greg


-----Original Message-----

Hi All,

Can anyone explain why the following code does not work
in a module:


Sub SelAll()

For Each Sht In ActiveWorkbook.Worksheets

Sht.Cells.Select

Next

End Sub



Whereas, the following code works fine in a worksheet
code:

Sub SelSht()

Cells.Select

End Sub


I am guessing it is related to scope, but I cannot see
why from my
reading of the excel help files.

Thanks in advance,

Alan.



.

Cells.Select - Why not in a module?
 

"Greg Wilson" wrote in
message ...

You cannot select cells in other than the active sheet.
You must first select the sheet before selecting the
cells. For example, this works:

Sub SelAll()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
Sht.Select
Sht.Cells.Select
Next
End Sub

Your second code example works because it references the
active sheet. The cells method, when not qualified,
refers to the active sheet; i.e. the
statement "Cells.Select" by default refers to the cells of
the active sheet.


Thanks Greg,

Much appreciated.

Regards,

Alan.

Cells.Select - Why not in a module?
 

"mudraker " wrote in
message ...

Alan

As far as I know you can not select on a sheet that is not active


try

Sub SelAll()
Dim sHt As Worksheet
For Each sHt In ActiveWorkbook.Worksheets
sHt.Activate
sHt.Cells.Select
Next
End Sub


Hi Mudraker,

Thanks - much appreciated.

Regards,

Alan.