Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change the reference to a named range via VBA?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change the reference to a named range via VBA?
Clarification:
The named range "Home" is a defined name in the worksheet, not a variable in any code. "Bassman62" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change the reference to a named range via VBA?
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change the reference to a named range via VBA?
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change the reference to a named range via VBA?
Thank you very much.
This helps a great deal! Best Regards "JLGWhiz" wrote in message ... 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change the reference to a named range via VBA?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change the reference to a named range via VBA?
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" 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" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I change the reference to a named range via VBA?
Thanks to Chip and Dave.
This helps my understanding of referencing named ranges in VBA. "Dave Peterson" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference first cell in a named range | Excel Worksheet Functions | |||
Named range in chart - any way to change the workbook reference with VBA | Excel Programming | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
named range, offset self-reference | Excel Discussion (Misc queries) | |||
Reference a named range | Excel Programming |