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.

use the INDIRECT() function

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



"jeff" wrote:

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.

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!*


"jeff" wrote:

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.

On Sep 3, 10:23*am, Luke M wrote:
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!*



"jeff" wrote:
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.- Hide quoted text -

- 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.

What error are you getting?

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