Worksheet Name RefersTo
 

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

Worksheet Name RefersTo
 

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


"caveman.savant" wrote:

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

Worksheet Name RefersTo
 

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?

:)

Worksheet Name RefersTo
 

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
---------------
Jacob Skaria


"caveman.savant" wrote:

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

Worksheet Name RefersTo
 

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.



"caveman.savant" wrote:

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

--

Dave Peterson