Sheet Array

[dennisli]

I use Array function to group four sheets Private, Commercial, Other and APD together. Then I want to loop through this array but got an error message about code:

For Each wsSheet in SheetArray.

The error message is: Run-time error ‘424’ Object required.
Any ideas will be welcome.
Dennis

Dim SheetArray As Variant
Dim wsSheet As Worksheet

SheetArray = Array("Private", "Commercial", "Other", "APD")
ThisWorkbook.Activate
    For Each wsSheet In SheetArray

 

[GlennUK]

You are setting up a string array, and then wanting the element of the array to be a worksheet. Change

Dim wsSheet As Worksheet

to

Dim wsSheet As Variant

and have

Sheets(wsSheet)

to refer to each sheet as required.

 

[Richard Schollar]

An array isn't an object - hence the error. You could instead use a For Next to loop thru the array eg:

Dim i As Long, vSheets As Variant
vSheets = Array("Private", "Commercial", "Other", "APD")
For i = LBound(vSheets) To UBound(vSheets)
  Sheets(vSheets(i)).Range("A1").Value = vSheets(i)
Next i

 

[dennisli]

thanks lot. have a good day.

 

[pgc01]

Hi

Another option is to work with a real array of sheets:

Dim SheetArray As Sheets, wsSheet As Worksheet
 
ThisWorkbook.Activate
Set SheetArray = Worksheets(Array("Sheet1", "Sheet3", "Sheet4"))
    
For Each wsSheet In SheetArray
    MsgBox wsSheet.Name
Next