Set Worksheet to variable name

[nikki333]

Hi Folks

I'm trying to loop trough worksheets e.g. in order to do some formatting for each of them

The problem is that i get an error 424 (Object required) each time.

The relevant lines of code look like so:




dim i as long
dim arrBlätter as variant
arrBlätter = Array("wsAllg", "wsGF", "wsFo1", "wsFo2", "wsFo3", "wsFo4")

For i = 1 To UBound(arrBlätter)

Set ws = arrBlätter(i)

With ws

...

End With

Next i




So the error comes in at this point: "Set ws = arrBlätter(i)"

Any idea what i'm missing here

Many thanks and cheerrs

[Paul_Hossler]

arrBlätter(I) is the Name of a worksheet, not the actual Worksheet





dim i as long
dim arrBlätter as variant

arrBlätter = Array("wsAllg", "wsGF", "wsFo1", "wsFo2", "wsFo3", "wsFo4")

For i = LBound(arrBlätter) To UBound(arrBlätter)

Set ws = Worksheets(arrBlätter(i))

With ws

...

End With

Next i

[rlv]

Another way to loop through the worksheets



Dim ws As Worksheet

For Each ws In Worksheets
Select Case ws.Name
Case "wsAllg", "wsGF", "wsFo1", "wsFo2", "wsFo3", "wsFo4"

...

End Select
Next ws

[nikki333]

Thank you Paul, that works.

However, since users might mess with worksheet names, i'm referring to the worksheet codenames in the array.
It seems that removing the quotation in marks in the array does the trick...



dim i as long, arrBlätter as variant

arrBlätter = Array(wsAllg, wsGF, wsFo1, wsFo2, wsFo3, wsFo4)

For i = LBound(arrBlätter) To UBound(arrBlätter)

Set ws = arrBlätter(i)

With ws

...

End With

Next i