Creating range name based on cell value.

[jeff]

Sheet named “Summary” contains a range name “TempNumber”.

I need a macro that will, within the current sheet, go to cell A1. It
will look at the range TempNumber and name the range (of cell A1) a
combination of the word “Vehicle” plus the value in TempNumber.

Example: The value in range TempNumber is "Test1".
This macro will cause Cell A1 of whatever the current sheet is to be
named “Vehicle Test1”
This will be used in different sheets, so I need it to do this in
whatever the Current Sheet is.

Thanks
j.o.

 

[Patrick Molloy]

use the INDIRECT() function

Activesheet.Range("A1").Name = "Vehicle" &
worksheets("Summary").Range("TempNumber").Value

 

[Luke M]

I believe you're looking for something like this:

Sub NameRange()

'Get Named Value
x = Range(ActiveWorkbook.Names("TempNumber"))

'Create Named Range
'Note that you can't have a space in name
ActiveWorkbook.Names.Add Name:="Vehicle" & x, _
RefersToR1C1:="=" & ActiveSheet.Name & "!R1C1"
End Sub

 

[jeff]

I believe you're looking for something like this:

Sub NameRange()

'Get Named Value
x = Range(ActiveWorkbook.Names("TempNumber"))

'Create Named Range
'Note that you can't have a space in name
ActiveWorkbook.Names.Add Name:="Vehicle" & x, _
RefersToR1C1:="=" & ActiveSheet.Name & "!R1C1"
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*








- Show quoted text -

Thanks for both solutions.
I'm getting an error when it comes to this part (same error on the
solution from Patrick).

ActiveWorkbook.Names.Add Name:="Vehicle" & x, _
RefersToR1C1:="=" & ActiveSheet.Name & "!R1C1"

I'll play around with this some more, but maybe there's something
obvious somebody can spot in the syntax.
Thanks
j.o.

 

[norie]

What error are you getting?

Both Patrick's and Luke's code work fine for me.

 

[jeff]

What error are you getting?

Both Patrick's and Luke's code work fine for me.

This is the whole sub. I probably should have put this in when I
originally posted, but I
wanted to keep it simple. I have noted the problem area below.
What I get is “Runtime error 1004. That name is not valid.”
Do I need a declaration in this module?

Thanks
j.o.

Sub NewSheet()

'Will id the car # based on next empty line
Application.Goto Reference:="VehicleMake"
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, -1).Range("A1").Select 'moves cursor left 1
'*****TEMPORARY RANGE NAME**********
ActiveWorkbook.Names.Add Name:="TempNumber", RefersTo:=ActiveCell

'Copies sheet "Vehicle template" to a new sheet
Sheets("Vehicle template").Select
Sheets("Vehicle template").Copy Before:=Sheets(3)

Worksheets("Vehicle template (2)").Name = Range
("TempNumber").Value


'Get Named Value
x = Range(ActiveWorkbook.Names("TempNumber"))

'**************these 2 lines are what is highlighted when macro is
run*******************
'Create Named Range
ActiveWorkbook.Names.Add Name:="Vehicle" & x, _
RefersToR1C1:="=" & ActiveSheet.Name & "!R1C1"
'****************************************************************
'Opens up UF so user can add data on new vehicle.
UserForm1.TextBox1.SetFocus
UserForm1.Show

End Sub

 

[jeff]

This is the whole sub. I probably should have put this in when I
originally posted, but I
wanted to keep it simple. I have noted the problem area below.
What I get is  “Runtime error 1004.  That name is not valid.”
Do I need a declaration in this module?

Thanks
j.o.

Sub NewSheet()

'Will id the car # based on next empty line
Application.Goto Reference:="VehicleMake"
Do Until IsEmpty(ActiveCell)
    ActiveCell.Offset(1, 0).Select
    Loop
ActiveCell.Offset(0, -1).Range("A1").Select 'moves cursor left 1
'*****TEMPORARY RANGE NAME**********
ActiveWorkbook.Names.Add Name:="TempNumber", RefersTo:=ActiveCell

'Copies sheet "Vehicle template" to a new sheet
Sheets("Vehicle template").Select
    Sheets("Vehicle template").Copy Before:=Sheets(3)

    Worksheets("Vehicle template (2)").Name = Range
("TempNumber").Value

  'Get Named Value
x = Range(ActiveWorkbook.Names("TempNumber"))

'**************these 2 lines are what is highlighted when macro is
run*******************
'Create Named Range
ActiveWorkbook.Names.Add Name:="Vehicle" & x, _
RefersToR1C1:="=" & ActiveSheet.Name & "!R1C1"
'****************************************************************
'Opens up UF so user can add data on new vehicle.
UserForm1.TextBox1.SetFocus
UserForm1.Show

End Sub

I was hoping somebody could help me with this one. I laid the whole
sub out here. I'm still getting an error. I've noted the place that
gets highlighted. Look for the *********** on the top and bottom of
the section in error. I have no idea how to correct this. Is this a
declaration issue?
Thanks
jeff