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