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

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

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

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

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

This might be more illustrative:


Public Sub ConsolidationTest()
Dim wkb As Workbook
Dim wks As Worksheet

Dim Mth_Area As String
Dim Ytd_Area As String
Dim FX_Mth_Area As String
Dim FX_YTD_Area As String
Dim Q1_Area As String
Dim Q2_Area As String
Dim Q3_Area As String
Dim Q4_Area As String

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


Set wkb = ThisWorkbook
Set wks = wkb.Sheets(1)


' Use following line of code to specify how to create your intended array
Call DoConsolidation(wkb, wks.Range("A1"), Mth_Area, _
Array("Sheet1", "Sheet2", "Sheet3"))

Call DoConsolidation(wkb, wks.Range("A2"), Ytd_Area, _
Array("Sheet1", "Sheet2", "Sheet3"))

Call DoConsolidation(wkb, wks.Range("A3"), FX_Mth_Area, _
Array("Sheet1", "Sheet2", "Sheet3"))

Call DoConsolidation(wkb, wks.Range("A4"), FX_YTD_Area, _
Array("Sheet1", "Sheet2", "Sheet3"))

Call DoConsolidation(wkb, wks.Range("A5"), Q1_Area, _
Array("Sheet1", "Sheet2", "Sheet3"))

Call DoConsolidation(wkb, wks.Range("A6"), Q2_Area, _
Array("Sheet1", "Sheet2", "Sheet3"))

Call DoConsolidation(wkb, wks.Range("A7"), Q3_Area, _
Array("Sheet1", "Sheet2", "Sheet3"))

Call DoConsolidation(wkb, wks.Range("A8"), Q4_Area, _
Array("Sheet1", "Sheet2", "Sheet3"))

End Sub



Public Sub DoConsolidation(wkb As Workbook, rngConsolidation As Range, _
strArea As String, arrList As Variant)
Dim 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
If (VarType(arrList(i)) = vbLong) Then
strElement = "'" & wkb.Path & "\[" & wkb.Name & "]" & _
wkb.Worksheets(Int(arrList(i))).Name & "'!" & strArea
Else
strElement = "'" & wkb.Path & "\[" & wkb.Name & "]" & _
wkb.Worksheets(arrList(i)).Name & "'!" & strArea
End If

arrParam(i) = strElement
Next i

rngConsolidation.Consolidate Sources:=arrParam, Function:=xlSum

End Sub




"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

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