For....Next- Array of worksheets.
 

I have the following code:

.....
Set H1 = Sheet2
Set H2 = Sheet4
Set H3 = Sheet6
Set H4 = Sheet8
Set Summary = Sheet11

For Each ws In Array(H1, H2, H3, H4)
.....

I have assigned the worksheet code names to variables H1..H4 and now wish to
cycle thru these worksheets. But XL is saying:

Runtime error 424: Object Required

On the For....Next line. What have I missed?

--

Julian Milano

For....Next- Array of worksheets.
 

Hi Julian,

Pls try like this.



Code:
--------------------

For Each ws In Worksheets(Array(H1.Name, H2.Name, H3.Name, H4.Name))

--------------------



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

For....Next- Array of worksheets.
 

Sub tester15()
Set H1 = Sheet1
Set H2 = Sheet11
Set H3 = Sheet111
Set H4 = Sheet1111
Set Summary = Sheet11111

For Each ws In Array(H1, H2, H3, H4)
Debug.Print ws.Name
Next
End Sub

Worked fine for me. Are you using the code name of the sheets?

If not you need to use
set H1 = Worksheets("Sheet2")

--
Regards,
Tom Ogilvy

Julian Milano wrote in message
...
I have the following code:

....
Set H1 = Sheet2
Set H2 = Sheet4
Set H3 = Sheet6
Set H4 = Sheet8
Set Summary = Sheet11

For Each ws In Array(H1, H2, H3, H4)
....

I have assigned the worksheet code names to variables H1..H4 and now wish
to
cycle thru these worksheets. But XL is saying:

Runtime error 424: Object Required

On the For....Next line. What have I missed?

--

Julian Milano

For....Next- Array of worksheets.
 

Thanks Tom,

I found the problem was in my declaration:

' Dim ws As workSheets

It doesn't like this line. I suppose "ws" should be declared as a variant?

--

Julian Milano


"Tom Ogilvy" wrote in message
...
Sub tester15()
Set H1 = Sheet1
Set H2 = Sheet11
Set H3 = Sheet111
Set H4 = Sheet1111
Set Summary = Sheet11111

For Each ws In Array(H1, H2, H3, H4)
Debug.Print ws.Name
Next
End Sub

Worked fine for me. Are you using the code name of the sheets?

If not you need to use
set H1 = Worksheets("Sheet2")

--
Regards,
Tom Ogilvy

Julian Milano wrote in message
...
I have the following code:

....
Set H1 = Sheet2
Set H2 = Sheet4
Set H3 = Sheet6
Set H4 = Sheet8
Set Summary = Sheet11

For Each ws In Array(H1, H2, H3, H4)
....

I have assigned the worksheet code names to variables H1..H4 and now
wish
to
cycle thru these worksheets. But XL is saying:

Runtime error 424: Object Required

On the For....Next line. What have I missed?

--

Julian Milano

For....Next- Array of worksheets.
 

Actually, ws should be declared as Worksheet (singular).

--
Regards,
Tom Ogilvy

"Julian Milano" wrote in message
...
Thanks Tom,

I found the problem was in my declaration:

' Dim ws As workSheets

It doesn't like this line. I suppose "ws" should be declared as a variant?

--

Julian Milano


"Tom Ogilvy" wrote in message
...
Sub tester15()
Set H1 = Sheet1
Set H2 = Sheet11
Set H3 = Sheet111
Set H4 = Sheet1111
Set Summary = Sheet11111

For Each ws In Array(H1, H2, H3, H4)
Debug.Print ws.Name
Next
End Sub

Worked fine for me. Are you using the code name of the sheets?

If not you need to use
set H1 = Worksheets("Sheet2")

--
Regards,
Tom Ogilvy

Julian Milano wrote in message
...
I have the following code:

....
Set H1 = Sheet2
Set H2 = Sheet4
Set H3 = Sheet6
Set H4 = Sheet8
Set Summary = Sheet11

For Each ws In Array(H1, H2, H3, H4)
....

I have assigned the worksheet code names to variables H1..H4 and now
wish
to
cycle thru these worksheets. But XL is saying:

Runtime error 424: Object Required

On the For....Next line. What have I missed?

--

Julian Milano

For....Next- Array of worksheets.
 

Check that - I was thinking of something else - yes, ws should be declared
as Variant.

If you wanted ws to be declared as worksheet, then you would do it this way:

Sub Tester2()
Dim ws As Worksheet
Set h1 = Worksheets(1)
Set h2 = Worksheets(2)
Set h3 = Worksheets(3)
For Each ws In Worksheets(Array(h1.Name, h2.Name, h3.Name))
Debug.Print ws.Name
Next
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Actually, ws should be declared as Worksheet (singular).

--
Regards,
Tom Ogilvy

"Julian Milano" wrote in message
...
Thanks Tom,

I found the problem was in my declaration:

' Dim ws As workSheets

It doesn't like this line. I suppose "ws" should be declared as a
variant?

--

Julian Milano


"Tom Ogilvy" wrote in message
...
Sub tester15()
Set H1 = Sheet1
Set H2 = Sheet11
Set H3 = Sheet111
Set H4 = Sheet1111
Set Summary = Sheet11111

For Each ws In Array(H1, H2, H3, H4)
Debug.Print ws.Name
Next
End Sub

Worked fine for me. Are you using the code name of the sheets?

If not you need to use
set H1 = Worksheets("Sheet2")

--
Regards,
Tom Ogilvy

Julian Milano wrote in message
...
I have the following code:

....
Set H1 = Sheet2
Set H2 = Sheet4
Set H3 = Sheet6
Set H4 = Sheet8
Set Summary = Sheet11

For Each ws In Array(H1, H2, H3, H4)
....

I have assigned the worksheet code names to variables H1..H4 and now
wish
to
cycle thru these worksheets. But XL is saying:

Runtime error 424: Object Required

On the For....Next line. What have I missed?

--

Julian Milano