Writing a dynamic Array in VBA

[MS_Xsmell]

Hey all, I'm using the Consolidate function to sum up a consistent range of data across multiple worksheets in a workbook. Here is my current code:

Sub Consl()
      
'Consolidated Sheets
    Selection.Consolidate Sources:=Array( _
    "'1'!R3C4:R6c20", "'2'!R3C4:R6c20", "'3'!R3C4:R6c20", "'4'!R3C4:R6c20", "'5'!R3C4:R6c20", "'6'!R3C4:R6c20", "'7'!R3C4:R6c20", _
    "'8'!R3C4:R6c20", "'9'!R3C4:R6c20", "'10'!R3C4:R6c20"), _
    Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

End Sub

I would like to improve this by making the number of sheets in the array dynamic. As an example, if I have 30 sheets ( named sheet 1 through 30) I would like them to be included in the array. I tried going the route of defining the number of tabs I want to include in the array:

For i = 1 To Range("TabCount")

I just can't figure out how to introduce it to the array.

Thanks in advance.

 

[JoeMo]

Maybe something like this:

Sub SheetsArray()
Dim i As Long, myArray()
ReDim myArray(1 To Sheets.Count)
For i = 1 To Sheets.Count
    myArray(i) = i
Next i
'the sheet index numbers are in myArray
MsgBox Join(myArray, ", ")
'test to return name of the sheet with index number 6
MsgBox Sheets(myArray(6)).Name
End Sub

 

[DanteAmor]

Try this:

Sub Cons_2()
  Dim i As Long, arr() As Variant
 
  ReDim arr(1 To 30)
  For i = 1 To UBound(arr)
    arr(i) = "'" & i & "'!R3C4:R6C20"
  Next
  Selection.Consolidate arr, xlSum, True, True, False
End Sub

 

[MS_Xsmell]

Try this:

Worked beautifully. I changed the hard code "30" to a named range with the number of sheets as follows:

 ReDim arr(1 To Range("tabcount"))

Cheers.

 

[DanteAmor]

Glad we could help & thanks for the feedback