The left column has product names and the top row has a column heading. Not all the products are on every worksheet. I need a way to add up the ones that are on multiple sheets (but different rows) and include the ones that are only on one sheet.
The consolidation function does this and I have it referencing the top and left cells for headings/columns.
Here is the code I have that does not work, it doesn't like my string in the array. But, if I type the string in it is fine.
(Works if I replace the last-
Array(ConRange) with
Array("Sheet1!R11C1:R27C4", "Sheet2!R11C1:R27C4", "Sheet3!R11C1:R27C4")
*****
GrpWorkSheets = Worksheets.Count
CurWorksheet = 2
Do While CurWorksheet<= GrpWorkSheets
Worksheets(CurWorksheet).Activate
sheetname = Worksheets(CurWorksheet).Name
rangename = Chr(34) & sheetname & Chr(33) & "R11C1:R27C4" & Chr(34) & ", "
ConRange = ConRange & rangename
CurWorksheet = CurWorksheet + 1
Loop
numChar = Len(ConRange)
numChar = numChar - 2
ConRange = Left(ConRange, numChar)
Worksheets(1).Activate
Range("A11").Select
Selection.Consolidate Sources:=Array(ConRange), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
This message was edited by Bruce on 2002-04-19 14:42