Sum a range across multiple Worksheets

[simora]

I have a sheet called "Totals" which sums all the worksheet totals for each cell between the sheets called "FIRST" and "LAST"

The formula that goes into each cell is like

=SUM(First:Last!D3)

Now what I want is to put the formula into each cell in a range so that it totals all the appropriate cells.

How can I re-write it in VBA so that if a cell's calculated value is 0 or nothing it just gives me a blank " " else it calculates the cell's value.

This works like this

Range("D3:E666").Formula = "=Sum(First:Last!D3)"

But when I try to do this to account for the cell's calculated value being zero

Range("D3:E666").Formula = "=IF(SUM(First:Last!D3)=0,"",=SUM(First:Last!D3))"

I get an error.

[Trebor76]

Hi simora,

Try this:

Range("D3:E666").Formula = "=IF(SUM(First:Last!D3)=0,"""",SUM(First:Last!D3))"

Regards,

Robert

[simora]

Thanks Robert.

It worked perfectly.

How would I re-write this .Formula = "=Sum(First:Last!D3)" So that the D3 refrences the ActiveCell in a Specific range.

This will be going into a loop on another worksheet.

[Trebor76]

I'm not really sure what you mean, but as a start this will put $B$2 into the formula:

.Formula = "=Sum(First:Last!" & Range("B2").Address & ")"

Does that help?

Robert

[simora]

Thanks Robert.

I got it to work like this

Cell.Formula = "=Sum(First:Last!" & Cell.Address & ")"

Really Appreciated your help on this.

[Trebor76]

You're welcome