Hi,

I'm trying to sum the same cell across a number of
worksheets using the following formula

=SUM(aaa:bbb!C1)
where aaa is the name of the first sheet, bbb is the name
of the last sheet and C1 is the cell being summed...
this works - but what I want to do is replace bbb with a
variable using the INDIRECT function. This means that I
can add sheets onto the end of the workbook (using VBA)
and by updating a single cell (again using VBA) all the
SUM formulas will now add through to the final sheet.

Can't get the syntax to work....any help appreciated

Rgds....Chris

Hi Chris;

You can use the paste special method and add the values
without using any functions or formulas. Here is an
example:

Sub try()
Sheets("SourceSheet").Select
Range("A1:B4").Select
Selection.Copy
Sheets("ResultsSheet").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False

End Sub

Thanks,

Greg
-----Original Message-----
Hi,

I'm trying to sum the same cell across a number of
worksheets using the following formula

=SUM(aaa:bbb!C1)
where aaa is the name of the first sheet, bbb is the name
of the last sheet and C1 is the cell being summed...
this works - but what I want to do is replace bbb with a
variable using the INDIRECT function. This means that I
can add sheets onto the end of the workbook (using VBA)
and by updating a single cell (again using VBA) all the
SUM formulas will now add through to the final sheet.

Can't get the syntax to work....any help appreciated

Rgds....Chris
.

Easier is to keep a blank sheet as the last sheet and insert any new sheets
before that, then you can do

=Sum(aaa:last!C1)

and not have to make an adjustment.

--
Regards,
Tom Ogilvy

"Chris Gorham" wrote in message
...
Hi,

I'm trying to sum the same cell across a number of
worksheets using the following formula

=SUM(aaa:bbb!C1)
where aaa is the name of the first sheet, bbb is the name
of the last sheet and C1 is the cell being summed...
this works - but what I want to do is replace bbb with a
variable using the INDIRECT function. This means that I
can add sheets onto the end of the workbook (using VBA)
and by updating a single cell (again using VBA) all the
SUM formulas will now add through to the final sheet.

Can't get the syntax to work....any help appreciated

Rgds....Chris