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")"

Not sure what you are looking for, but this will assign a variable to the
range and then name it.

Set sh = Sheets(ActiveSheet.Name)
With sh
Set myVar = Range("B11", Range("B11").End(xlDown).Offset(-1, 0))
ActiveWorkbook.Names.Add Name:=ActiveSheet.Name + "Days", _
RefersToR1C1:=myVar.Address
Range("F10").NumberFormat = "0.00%"
Range("F10") = WorksheetFunction.Sum(myVar)
End With



"kevlarmcc" <(E-Mail Removed)> wrote in message
news:60FA0473-7ABC-4663-857E-(E-Mail Removed)...
>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")"
>
>

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")"
>
>