Greetings:

I wrote the below macro which uses the For Each Next Loop. The purpose of
the macro is to select a specific cell on each worksheet except the last
worksheet in the workbook. I am surprised that in order for it to work, my
macro has to select the first sheet in the selection before entering the For
Each Next Loop and then use the ActiveSheet.Next.Select instruction in the
For Each Next Loop to move on to the next sheet to be processed. LstRowData
was computed elsewhere and is the last row of the worksheet which contains
the input data.

Sub CheckForEachNext()
'
' Hotkey: Ctrl+Shift+J
'
Dim LstRowData As Long
'Without the below instruction and the ActiveSheet.Next.Select instruction,
only the
' activesheet is processed. With the below instruction, but still without
ActiveSheet.Next.Select, only the first worksheet is processed.

ActiveWorkbook.Worksheets(1).Select
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
If ActiveSheet.Name < "Sheet1" Then
LstRowData = Range("O2")
Cells(LstRowData + 9, "W").Select

' With the below ActiveSheet.Next.Select instruction but without the above
' ActiveWorkbook.Worksheets(1).Select instruction, only the ActiveSheet
through
' the last sheet are processed.

ActiveSheet.Next.Select
Else
Exit For
End If
Next WS
MsgBox "I am done"
End Sub

--
All suggestions will be greatly appreciated. May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

Try this:

For Each WS In ActiveWorkbook.Worksheets
If WS.Name < "Sheet1" Then
LstRowData = Range("O2")
Cells(LstRowData + 9, "W").Select
End If
Next


Tom

Hi Tom:
I like your suggestion. Unfortunately my Excel VBA doesn't. When I ran
your code, only the activesheet had the specified cell selected. When I added
ActiveWorkbook.Worksheets(1).Select before entering the For Each Loop, then
only Worksheets(1) had the specified cell selected. Then when I added
ActiveSheet.Next.Select just after the cell selection, then all the
worksheets had the specified cell selected. Could the implementation of the
For Each Loop itself be defective in my version of Excel? I have the
Microsoft Office Home and Student 2007 version.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick


"tompl" wrote:

Try this:

For Each WS In ActiveWorkbook.Worksheets
If WS.Name < "Sheet1" Then
LstRowData = Range("O2")
Cells(LstRowData + 9, "W").Select
End If
Next


Tom

The problem is in the definition of LstRowData. Your question did not
explain how it gets it's value. I think you need to put the definition of
LstRowData within the loop. Something like:

For Each WS In ActiveWorkbook.Worksheets

LstRowData = WS.range("O2")

If WS.Name < "Sheet1" Then
LstRowData = Range("O2")
Cells(LstRowData + 9, "W").Select
End If
Next

Tom

Also consider:


WS.Cells(LstRowData + 9, "W").Select

Although I have no idea why you are selecting this cell.

Tom

Hi Tom:
Greetings! The basic problem is that the For Each Next loop will not
proceed to the next worksheet, unless I add that ActiveSheet.Next.Select
instruction. Why should that instruction be necessary? Unless I add that
instruction and also put
ActiveWorkbook.Worksheets(1).Select before entering the For Each Next loop
the macro will not work. Once I add those two instructions, the macro works
perfectly. By selecting the cell Cells(LstRowData + 9, "W") on each sheet, it
is very easy for me to quickly flip through the worksheets and quickly
compare them. As long as LstRowData has a value just before the cell
selection, cell selection works just fine.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick


"tompl" wrote:

Also consider:


WS.Cells(LstRowData + 9, "W").Select

Although I have no idea why you are selecting this cell.

Tom

You need to add the line:

WS.select within the loop before the range select.

See my most recent post.

Tom

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

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

Try the below..

Sub Macro()
Dim ws As Worksheet, lngRow As Long
For intSheet = 1 To Sheets.Count - 1
lngRow = Sheets(intSheet).Cells(Rows.Count, "W").End(xlUp).Row
Application.Goto Sheets(intSheet).Range("G" & lngRow + 9)
Next
Sheets(1).Activate
End Sub


--
Jacob


"MichaelDavid" wrote:

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