For....Next- Array of worksheets.

  • Thread starter Thread starter Julian Milano
  • Start date Start date
J

Julian Milano

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?
 
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")
 
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?
 
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
 
Back
Top