Give array a Name and use in code

[Cammandk]

I have the following line of code that selects the 2 sheets.

[For Each sh In Sheets(Array("Sch6", "Sch7"))]

Can I create a list of sheets and name this range and then just have the one name in the above formula.

Thanks
DK

 

[Luke M]

Yep! Here's an example of the syntax:

Sub ArrayExample()
'Variable types defined
Dim myArray As Sheets
Dim sh As Worksheet

'Variable defined
Set myArray = Worksheets(Array("Sheet2", "Sheet1"))
'Use the variable
For Each sh In myArray
    MsgBox sh.Name
Next
End Sub

 

[Cammandk]

Hi Luke
Not sure if this is what I'm after - maybe I wasn't clear enough.
On a Worksheet I want to have a list in A1:A3 - "Sheet1", "Sheet2", "Sheet3". I want to name this range "ArrayList"

I then want to use this name "ArrayList" in the code to replace all the sheets as I may have 30 sheets in the array and it won't be all the sheets in the Workbook.

Thanks
DK

 

[dan_l]

I'm still not getting it?

dim avList as variant
dim rList as range

set rlist = sheets("sheet1").range("a1:a3")
avlist = rlist

 

[Luke M]

I believe you would need to populate the array from the range. Something like this should work:

Sub SheetArrays()
Dim myRange As Range
Dim myArray As Sheets
Dim sh As Worksheet
Dim myArrayNames()
'Where is the range of cells
Set myRange = Worksheets("Sheet3").Range("A1:A2")
x = myRange.Cells.Count
'Setup our array to be populated
ReDim myArrayNames(1 To x)
'Load the array with names
For i = 1 To myRange.Cells.Count
  myArrayNames(i) = myRange.Cells(i).Value
Next
'Example of using this array
Set myArray = Worksheets(myArrayNames)
For Each sh In myArray
  MsgBox sh.Name
Next
End Sub