Store sheet names in Array

[urobee]

Hy, I need to make an array with some of the sheets of the workbook.

I have a working code but i want to make a shorter and faster version of it.
So, this is my code now:

This make an array from the listed sheets: (ShABC0, ShABC1, etc...)
(the sheets are set on another sub, named: LoadSheets)
The sheet is selected based on the selected multipage-page on a userform then get data from a given range of the sheet to make a chart on the userform.
(It's easier then it sounds )

Dim ChartData(1 To 2) As Range
Dim ChartName(1 To 2) As String
Dim i As Integer
Dim Ar As Variant

LoadSheets

Ar = Array(ShABC0, ShABC1, ShABC2, ShABC3, ShABC4, ShABC5)


i = MultiPage1.Value


Select Case i


Case i


Set ChartData(1) = Ar(i).Range("B40:AE40")
ChartName(1) = Ar(i).Range("K3")
Set ChartData(2) = Ar(i).Range("B35:AE35")
ChartName(2) = Ar(i).Range("A35")


End Select

'.....Rest of the code....

My problem is the Ar = Array(ShABC0, ShABC1, etc...) part, because there are much more sheets in the workbook then currently in this code.

That would i like to do:
If SheetName Like "*ABC*" Then
make the array and use the selected sheet in the CASE


So in this way I won't need to set the sheets one by one.

I hope You can help me!
Thanks!

 

[footoo]

https://www.mrexcel.com/forum/excel-questions/75445-sheet-names-into-array.html

 

[urobee]

thanks!

I use this code:

Sub sheetray()    Dim SNarray, i
    ReDim SNarray(1 To Sheets.count)
    For i = 1 To Sheets.count
        SNarray(i) = ThisWorkbook.Sheets(i).Name
Debug.Print SNarray(i)
    Next
End Sub

In the inmediate window i get the needed sheets, but i can't able to find out how i will refer to this array in my case.

 

[Fluff]

How about

   Dim Lst As Object
   Dim ws As Worksheet
   
   Set Lst = CreateObject("system.collections.arraylist")
   For Each ws In Worksheets
      If ws.name Like "*ABC*" Then Lst.Add ws.name
   Next ws
   Lst.Sort
   
   MsgBox Lst(0)

 

[urobee]

Thanks!
This is works too (it is list the sheets I need), but my problem is the same, i can't able to find out how i will refer to this array in the CASE.

I deleted the last line of your code (MsgBox Lst(0) ) and tried to use it but i get an error message (Object required) in the line of the code when it is want to use the sheets from the array.
This is the line when i get the error message:

[/COLOR][COLOR=#333333]Set ChartData(1) = Lst(i).Range("B40:AE40")[/COLOR][COLOR=#333333]

You can check my full code in the first post, the only change i made: Replace Ar to Lst.

 

[Fluff]

Try

Sheets(lst(i)).range

 

[urobee]

Yeah, it seems to be good!
Thank You very much!

 

[Fluff]

Glad we could help & thanks for the feedback