Hi,

My workbook consists of 10 sheets. The names of the sheets a 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

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

"Paul" wrote in message
om...
Hi,

My workbook consists of 10 sheets. The names of the sheets a 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

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

"Paul" wrote in message
om...
Hi,

My workbook consists of 10 sheets. The names of the sheets a 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