I have a range named "Home" that refers to: =Sheet1!$A$1.
What Macro code would I use to change the reference?
Thanks for any advice.

Clarification:
The named range "Home" is a defined name in the worksheet, not a variable
in any code.

"Bassman62" <(E-Mail Removed)> wrote in message
news:O$(E-Mail Removed)...
>I have a range named "Home" that refers to: =Sheet1!$A$1.
> What Macro code would I use to change the reference?
> Thanks for any advice.
>
>

Here is how I do it. Say you want to move the named range from A2 to B3.

Sub dk()
ActiveWorkbook.Names("Home").Delete
ActiveWorkbook.Names.Add "test", RefersTo:="=Sheet1!$B$3"
End Sub

It will also work for a range like Sheet1!$A$2:$C$12


"Bassman62" <(E-Mail Removed)> wrote in message
news:O$(E-Mail Removed)...
>I have a range named "Home" that refers to: =Sheet1!$A$1.
> What Macro code would I use to change the reference?
> Thanks for any advice.
>
>

Sorry about the typo, should have been Home in both places.

Sub dk()
ActiveWorkbook.Names("Home").Delete
ActiveWorkbook.Names.Add "Home", RefersTo:="=Sheet1!$B$3"
End Sub



"Bassman62" <(E-Mail Removed)> wrote in message
news:O$(E-Mail Removed)...
>I have a range named "Home" that refers to: =Sheet1!$A$1.
> What Macro code would I use to change the reference?
> Thanks for any advice.
>
>

Thank you very much.
This helps a great deal!
Best Regards


"JLGWhiz" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Sorry about the typo, should have been Home in both places.
>
> Sub dk()
> ActiveWorkbook.Names("Home").Delete
> ActiveWorkbook.Names.Add "Home", RefersTo:="=Sheet1!$B$3"
> End Sub
>
>
>
> "Bassman62" <(E-Mail Removed)> wrote in message
> news:O$(E-Mail Removed)...
>>I have a range named "Home" that refers to: =Sheet1!$A$1.
>> What Macro code would I use to change the reference?
>> Thanks for any advice.
>>
>>
>
>

You can just reassign the Name to a different range:

worksheets("sheet99").range("z9").name = "Home"




Bassman62 wrote:
>
> I have a range named "Home" that refers to: =Sheet1!$A$1.
> What Macro code would I use to change the reference?
> Thanks for any advice.

--

Dave Peterson

> ActiveWorkbook.Names("Home").Delete

You don't need to delete the name, although it is harmless to do so
and it adds clarity to the logic of the code.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sat, 21 Mar 2009 19:33:40 -0400, "JLGWhiz" <(E-Mail Removed)>
wrote:

>Sorry about the typo, should have been Home in both places.
>
>Sub dk()
> ActiveWorkbook.Names("Home").Delete
> ActiveWorkbook.Names.Add "Home", RefersTo:="=Sheet1!$B$3"
>End Sub
>
>
>
>"Bassman62" <(E-Mail Removed)> wrote in message
>news:O$(E-Mail Removed)...
>>I have a range named "Home" that refers to: =Sheet1!$A$1.
>> What Macro code would I use to change the reference?
>> Thanks for any advice.
>>
>>
>

Thanks to Chip and Dave.
This helps my understanding of referencing named ranges in VBA.

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You can just reassign the Name to a different range:
>
> worksheets("sheet99").range("z9").name = "Home"
>
>
>
>
> Bassman62 wrote:
>>
>> I have a range named "Home" that refers to: =Sheet1!$A$1.
>> What Macro code would I use to change the reference?
>> Thanks for any advice.
>
> --
>
> Dave Peterson