Consolidation Function VBA

Q

QuietMan

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
 
B

Bill Pfister

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
 
Q

QuietMan

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 said:
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 said:
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
Click to expand...
Click to expand...
 
Q

QuietMan

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 said:
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 said:
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
Click to expand...
Click to expand...
 
B

Bill Pfister

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


QuietMan said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Bill Pfister

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Q

QuietMan

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 said:
Which sheets will be associated with each of the 8 areas?


QuietMan said:
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 said:
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




:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Consolidation Array Help 0
Consolidation Array 0
data consolidation using vba 0
Consolidation refresh (update) 0
Data Consolidation using dynamic path 1
Dynamic Array for data consolidation 2
Using an array in Consolidation 0
Consolidation Problem 0

Top