Consolidate Sources

[Paul]

Hi,

My workbook consists of 10 sheets. The names of the sheets are: Total,
Jan03, Feb03, Mar03, Apr03, May03, Jun03, Jul03, Aug03 and List. The
workbook will expand with a new sheet each new month (Oct03, Nov03,
Des03, Jan04....)

The sheet named Total is used to consolidate the range B2:C65536 in
all sheets, but not the sheet named List. By today my code look like:

Sub Makro1()
'
Sheets("Total").Select
Range("B2").Select
Selection.Consolidate Sources:=Array( _
"'C:\Documents and Settings\[Bok2.xls]Apr03'!R2C2:R65536C3", _
"'C:\Documents and Settings\[Bok2.xls]Aug03'!R2C2:R65536C3", _
"'C:\Documents and Settings\[Bok2.xls]Feb03'!R2C2:R65536C3", _
"'C:\Documents and Settings\[Bok2.xls]Jan03'!R2C2:R65536C3", _
"'C:\Documents and Settings\[Bok2.xls]Jul03'!R2C2:R65536C3", _
"'C:\Documents and Settings\[Bok2.xls]Jun03'!R2C2:R65536C3", _
"'C:\Documents and Settings\[Bok2.xls]Mar03'!R2C2:R65536C3", _
"'C:\Documents and Settings\[Bok2.xls]May03'!R2C2:R65536C3") _
, Function:=xlSum, TopRow:=False, LeftColumn:=True,
CreateLinks:=False
End Sub

I don't know much about Array functions, but isn't there an easy way
to make a code that will consolidate all sheets in the workbook except
those two named Total and List?

Any help would be much appreciated.

Regards,
Paul

 

[Don Guillett]

why not a dummy sheet after the last inserted sheet and
=sum(sheet1:last!b1) where b1 has the total for b2:c65536

 

[Bradley Dawson]

Try this:

Sub Totals()
Dim i%, SheetArg$()
ReDim SheetArg(2 To Worksheets.Count-1)
For i = 2 To Worksheets.Count-1
SheetArg(i) = Worksheets(i).Name & "!R2C2:R65536C3"
Next i
Sheets("Total").Select
Range("B2").Select
Worksheets("Sheet1").Range("A1").Consolidate _
Sources:=Array(SheetArg), _
Function:=xlSum
End Sub

 

[Bradley Dawson]

Thank you, for giving me the opportunity to learn about the consolidate
function. It is really powerful!

 

[Paul]

Hi again.

I've a follow-up question, and would appreciate if some of you could
help me with this as well.

Per today 20 workbooks are located in d:\timelist\data. All workbooks
have one sheet named Total.

I've also a workbook named Summary.xls that is located in d:\timelist.

Is it possible to make a macro in Summary.xls (and the sheet named
SumTotal) that can consolidate all sheets (range B2:C65536) named
Total in all workbooks located in d:\timelist\data?

I know that I can specify each work book in a macro like:

Sub Makro1()
Sheets("SumTotal").Select
Range("B2").Select
Selection.Consolidate Sources:=Array( _
"'D:\timelist\data\[Bok1.xls]Total'!R2C2:R65536C3", _
"'D:\timelist\data\[Bok2.xls]Total'!R2C2:R65536C3", _
.....
and so on
.....
, Function:=xlSum, TopRow:=False, LeftColumn:=True,
CreateLinks:=False
End Sub

But, since I could get a new file next month located in
d:\data\timelist, or even delete one, this is not appropriate.

Regards,
P

 

[Don Guillett]

While in that folder, something like

for each wb in workbooks
if wb.name <>"Summary.xls" then
next wb

 

[Tom Ogilvy]

Sub Totals()
Const MAXBOOK As Long = 20
Dim i%, SheetArg$()
Dim sPath1 As String
ReDim SheetArg(1 To MAXBOOK)


Dim sPath As String, sFile As String
ThisWorkbook.Worksheets("SumTotal") _
.Cells.ClearContents
sPath = "D:\Timelist\Data\"
i = 0
sPath1 = "D:\TimeList\Data\*.xls"
sFile = Dir(sPath1)
Do While sFile <> ""
i = i + 1
SheetArg(i) = "'" & sPath & _
"[" & sFile & "]Total'!R1C2:R16384C3"

sFile = Dir()
Loop
' For i = 1 To MAXBOOK
' Debug.Print i, SheetArg(i)
' Next

ThisWorkbook.Sheets("SumTotal"). _
Range("A1").Consolidate _
Sources:=Array(SheetArg), _
Function:=xlSum, _
TopRow:=False, _
LeftColumn:=False, _
CreateLinks:=False

End Sub


this only does to row 16384

It doesn't seem to want to work using closed workbooks and down to 65536.

I suspect there might be a problem with the number of external links.

Think you need to be more conservative in the number of rows you want to
attack.

change MAXBOOK to equal the number of books in the directory.

I only tested it with 5 workbooks.

Remarks,
Tom Ogilvy