hi
use a variable.
Range("B11").Select
Range(Selection, Selection.End(xlDown).Offset(-1#)).Select
Dim strng As String '**********
strng = ActiveSheet.Name & "Days"

ActiveWorkbook.Names.Add Name:=strng, _
RefersToR1C1:=Selection

Range("F10").NumberFormat = "0.00%"
Range("F10").FormulaR1C1 = "=Sum(" & strng & ")"

regards
FSt1


"kevlarmcc" wrote:

I am trying to write a macro that selects a range of cells and then format a
nearby cell to show the sum of that range. The code i have does so by
selecting the range, naming it, and using the name in the formula. The
problem is that I want to run this for multiple sheets in the workbook. I
can't figure out how to use a variable to name the range and use it in the
sum formula. Using ActiveSheet.name + "Days" in the sum formula creates an
error.

Range("B11").Select
Range(Selection, Selection.End(xlDown).Offset(-1#)).Select

ActiveWorkbook.Names.Add name:=ActiveSheet.name + "Days", _
RefersToR1C1:=Selection

Range("F10").NumberFormat = "0.00%"
Range("F10").FormulaR1C1 = "=Sum(ActiveSheet.name + "Days")"