Worksheet Name RefersTo

[caveman.savant]

I'm trying to select a range of cells and name them based on the name
of the worksheet

Sub NameBlock()
myLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
myLastCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
ThisWorkbook.Names.Add Name:=Worksheets(1).Name + "_total", _
RefersTo:="=$A$2:"&myLastRow, &myLastCol, Visible:=True
End Sub

Something is wrong

 

[joel]

Try this instead. Your columns are numeric and should be a letter.

Sub NameBlock()
myLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
myLastCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
Set MyRange = Range(Range("A1"),cells(myLastRow,myLastCol))

ThisWorkbook.Names.Add Name:=Worksheets(1).Name + "_total", _
RefersTo:="=" & MyRange.Address, Visible:=True
End Sub

 

[Fernando Fernandes]

The problem is the code you are using, the refersto has invalid content, as
the other user also said, but if you resolve your string, it would become:
"=$A$2:"&myLastRow, &myLastCol
if last row is 1000 and last column is 10
then
=$A$2:1000, 10 'which makes no sense! you should be getting an error in this
line of code, right ?

You should use the suggested above, or:
"=$A$2:" & Worksheets(1).Cells(myLastRow, myLastCol).address

Makes sense?

 

[Jacob Skaria]

The reference is wrong. Refer the range as

Dim rngTemp as Range

Set rngTemp = Range(Cells(2, 1), Cells(myLastRow, myLastCol))

and set the reference as RefersTo:= "=" & rngTemp.Address

If this post helps click Yes

 

[caveman.savant]

Joel's solution worked for me. Taking this further...

Sub CreateBlock()
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
lngLastCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
ActiveSheet.Cells(lngLastRow, lngLastCol + 1).Formula = _
WorksheetFunction.Sum(Range(Cells(2, 2), Cells(lngLastRow,
lngLastCol)))
Set MyRange = Range(Range("B2"), Cells(lngLastRow, lngLastCol))
ThisWorkbook.Names.Add Name:=Worksheets(1).Name + "_rng", _
RefersTo:="=" & MyRange.Address, Visible:=True
' Name the cell with the total of the range as Worksheets(1).Name +
"_ttl"
' Drop Down 2 rows
' Place value of Worksheets(1).Name + "_ttl" in this cell
' name this cell Worksheets(1).Name + "_trg"
' Open a new sheet
' Select the same range of cells as the 1st sheet
' Copy the formula "=SUM(Sheet1!B2/2)" into each cell
End Sub

 

[Dave Peterson]

I like this syntax:

Sub NameBlock2()
Dim myLastRow as long
dim myLastCol as long

with activesheet
myLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
myLastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
.range("A2",.cells(mylastrow,mylastcol)).name = .name & "_total"
End with
End Sub



worksheets(1) is the activesheet, right?

And when you use it in a name, it's valid, right?

You may want to use a worksheet level name and not have to worry about the name
of the sheet creating a valid name:

Sub NameBlock3()
Dim myLastRow as long
dim myLastCol as long

with activesheet
myLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
myLastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
.range("A2",.cells(mylastrow,mylastcol)).name = "'" & .name & "'!_total"
End with
End Sub

ps. VBA is very forgiving--but not always.

You may want to start using & to concatenate strings and + to add values.