Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
issue with RefersTo in non-EN languages | Excel Programming | |||
refersTo property | Excel Programming | |||
Help building string for Names.Add RefersTo, pls? | Excel Programming | |||
Wierd named range RefersTo value | Excel Programming | |||
Wierd named range RefersTo value | Excel Programming |