| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidation Function VBA
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Consolidation Function VBA
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Consolidation Function VBA
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Consolidation Function VBA
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Consolidation Function VBA
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Consolidation Function VBA
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Consolidation Function VBA
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Consolidation function | Excel Programming | |||
| consolidation | Excel Discussion (Misc queries) | |||
| Consolidation | Excel Discussion (Misc queries) | |||
| Product Function in Pivot Tables from Multiple Consolidation Range | Excel Worksheet Functions | |||
| Consolidation | Excel Programming | |||
Linear Mode
