Sheet Array

D

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
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:

Code: 
For Each wsSheet in SheetArray.

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


Code: 
Dim SheetArray As Variant
Dim wsSheet As Worksheet

SheetArray = Array("Private", "Commercial", "Other", "APD")
ThisWorkbook.Activate
    For Each wsSheet In SheetArray
 
Sort by date Sort by votes
You are setting up a string array, and then wanting the element of the array to be a worksheet. Change
Code: 
Dim wsSheet As Worksheet
to
Code: 
Dim wsSheet As Variant
and have
Code: 
Sheets(wsSheet)
to refer to each sheet as required.
 
Upvote 0
An array isn't an object - hence the error. You could instead use a For Next to loop thru the array eg:

Code: 
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
 
Upvote 0
Hi

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

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
VBA Loop and Msgbox issue
Replies
3
Views
153
saboh12617
S
A
  • Question Question
VBA for sending e-mails
Replies
5
Views
644
Micron
M
C
  • Solved
Help! Can't pass a range of cell strings into a variant array/can't reference the strings in this array - myarray(I,1) gives subscript error
Replies
3
Views
112
Peter_SSs
Peter_SSs
M
  • Solved
mismatch error when using sheets in array to populate data on userform
2
Replies
13
Views
216
Mussala
M
S
  • Solved
Worksheet_Change Event for 2D pasted array
Replies
2
Views
117
St Jimmy
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top