For Each Next Loop behaving in unexpected manner
Hi Tom:
I played around with the code for a few hours, and here is what I came up
with:
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
If WS.Name < "Sheet1" Then
LstRowData = WS.Range("O2")
WS.Activate
WS.Cells(LstRowData + 9, "W").Select
End If
Next WS
which is very similar to what you came up with.
Apparently the WS.Activate instruction is absolutely necessary. Excel does
not allow
a cell to be selected unless the worksheet it is on has been activated.
Perhaps you can come up with a way to omit WS.Activate instruction. Just
removing the WS.Activate instruction results in the error message: "Run-time
error '1004': Select method of Range class failed." Thanks for all your kind
help.
--
May you have a most blessed day!
Sincerely,
Michael Fitzpatrick
"tompl" wrote:
I think I get it now. First you have to select the worksheet, then you can
select the cell.
Try this:
For Each WS In ActiveWorkbook.Worksheets
If WS.Name < "Sheet1" Then
LstRowData = Range("O2") 'if LstRowData was computed elsewhere why is
'this here? should it be WS.Range("O2") = LstRowData? I think this line
'might need to be eliminated here. Or identify what sheet Range("O2") is on.
WS.select
Cells(LstRowData + 9, "W").Select
End If
Next
|