Thanks Bill, I will try this
I'm new to using arrays, so cold be some issues

Wilkl let you know jow it goes

--
Helping Is always a good thing


"Bill Pfister" wrote:

Using an intermediate array-creating function should minimize code duplication:




Public Sub ConsolidationTest()
Dim strAreas2 As Variant
Dim wkb As Workbook

Set wkb = ThisWorkbook


' Use following line of code to specify how to create your intended array
' The 2nd param ("R3C1") is the source address
' The 3rd param (Array(1, 2, 3)) is the indices of the source sheets
Call CreateArray(wkb, "R3C1", Array(1, 2, 3), strAreas2)

wkb.Sheets(1).Range("A1").Consolidate Sources:=strAreas2, Function:=xlSum

End Sub


Public Sub CreateArray(wkb As Workbook, strArea As String, _
arrList As Variant, arrParam As Variant)
Dim strElement As String
Dim lngCount As Long
Dim i As Long

lngCount = UBound(arrList) - LBound(arrList)

ReDim arrParam(0 To lngCount) As String

For i = 0 To lngCount
strElement = "'" & wkb.Path & "\[" & wkb.Name & "]" & _
wkb.Worksheets(Int(arrList(i))).Name & "'!" & strArea
arrParam(i) = strElement
Next i

End Sub




"QuietMan" wrote:

Dose anyone know how to simplify this function??
I have to write the function 8 times (consolidation 8 seperate areas on a
spreadsheet) the only parts that change are the sheet numbers and the
consolidation area.

Example: all sheets consolidated for Q1, 3 sheets for Q2, ect....

I hoping to be able to have the array that's being created be dynamic based
on information located in a specified worksheet.

thanks in advance

Selection.Consolidate Sources:=Array( _
"'" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" &
Sheet38.Name & "'!" & Q3_Area, _
"'" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" &
Sheet36.Name & "'!" & Q3_Area, _
"'" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" &
Sheet37.Name & "'!" & Q3_Area, _
"'" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" &
Sheet34.Name & "'!" & Q3_Area, _
"'" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" &
Sheet31.Name & "'!" & Q3_Area, _
"'" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" &
Sheet35.Name & "'!" & Q3_Area, _
"'" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" &
Sheet25.Name & "'!" & Q3_Area, _
"'" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" &
Sheet28.Name & "'!" & Q3_Area), _
Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False


--
Helping Is always a good thing