MS_Xsmell
Board Regular
- Joined
- Jan 27, 2007
- Messages
- 76
- Office Version
- 365
- Platform
- Windows
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:
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:
I just can't figure out how to introduce it to the array.
Thanks in advance.
VBA 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:
VBA Code:
For i = 1 To Range("TabCount")
Thanks in advance.