Cells.Select - Why not in a module?

[Alan]

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.

 

[mudraker]

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

 

[Greg Wilson]

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

 

[Alan]

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.

 

[Alan]

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.