VBA to create formula from a list in one sheet.

[Scott French]

I am looking for a script that will create a formula that adds up cells from
multiple sheets and places the formula on a totals sheet. The script needs to
grab the name of the sheets from a range on a seperate sheet in the workbook.

I hope this make sense.

 

[Bob Phillips]

Range("A1").Formula =
"=SUMPRODUCT(SUMIF(INDIRECT(""'""&C1:C3&""'!C8""),""<>""))"

where C1:C3 is a range housing the relevant sheetnames in separate cells,
and C* is the cell to add.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Scott French]

Thanks Bob that worked.
One tweek I can't figure out is how to get the script to create the range
"C1:C3" in the formula automatically. The list can vary from 1 to many sheet
names. I tried to C1:C50 but if cells below C5 are empty you get a REF error.

TIA

Scott

 

[Bob Phillips]

Scott,

This should do it

Dim LastRow As Long
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Range("A1").Formula = _
"=SUMPRODUCT(SUMIF(INDIRECT(""'""&C1:C" & LastRow & "&""'!C8""),""<>""))"



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)