Hi, you can use "offset" combined with counta to give dynamic number of rows:
RefersToR1C1:= "=Offset('February 2015!R7C26,0,0,counta('February 2015!R7C26:R10000C26) - 1,1)"
As for the names per sheet thing... I can't say I really use "worksheet" scoped names, they seem to give me a headache... but try something like this: i.e. instead of adding name from the workbook object, add from the worksheet:
for each wsSheet in thisworkbook.sheets
wsSheet.names.add Name:="monthlyclaim", RefersToR1C1:= "=Offset('" & wssheet.name & "'!R7C26,0,0,counta('" & wssheet.name & "'!R7C26:R10000C26) - 1,1)"
Rinse and repeat for other name... So kinda conceptually different but same effect: any formulas on those sheets that reference "monthlyclaim" will match a worksheet scoped name first.
Won't this give the wrong answer if there is a blank cell in the list?
true... Dan's answer below might be more suitable in that case (if there are blanks)
I was thinking that if they were all numbers you could use MATCH(9.999999999999999999E+307,Z7:Z26) to get the row of the total instead of counting entries.
or if there's a total row there's probably some text "Total:" or something somewhere you can search for or match on