Thanks again bill, I think I should be abel to make some serious progress
with the last routines you wrote

The 8 areas on the same sheet
but within those 8 areas the number of sheets that being consolidated will
change

example: sheet = brand summary
on the brand summary sheet i will have to consolidate results
for 8 different areas, in Mth_area I consolidate result for 4 brands ( =
4 sheets)
In the YTD_Area for 10 brands, (the number of brands to consolidate for
each area will change)
--
Helping Is always a good thing


"Bill Pfister" wrote:

Which sheets will be associated with each of the 8 areas?


"QuietMan" wrote:

Bill,

Having a little trouble understanding the code, below are the 8 sources
areas that will be consolidated from multiple sheets to 8 different areas on
sheet where the consolidation will take place. I'm lost trying to
incorporate this into the code

each of these areas can combine from 2 to 20 sheets

thanks

Mth_Area = "R9C3:R62C14" goes to R9C3
Ytd_Area = "R9C16:R61C36" goes to R9C16
FX_Mth_Area = "R16C38:R62C44" goes to R16C38
FX_YTD_Area = "R16C46:R62C52" goes to R16C46
Q1_Area = "R9C54:R62C57" goes to R9C54
Q2_Area = "R9C59:R62C62" goes to R9C59
Q3_Area = "R9C64:R62C67" goes to R9C64
Q4_Area = "R9C69:R62C77" goes to R9C69
--
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