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 )
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!
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 )
Code:
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!