| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
renaming a Named Range in multiple workbooks
Hello all
I want to be able to rename a "Named Range" (Someone spelled it wrong) and now I have 1600+ workbooks that I need to change. This could be multiple places in the workbook itself. I was thinking that I could possibly do it with VB 6 or VB 2008. I was hoping that someone may have already had this issue and a quick solutions... If not then it's on to the coding... Thanks in advance. Rob |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
renaming a Named Range in multiple workbooks
hi Tagwim
See this page http://www.rondebruin.nl/copy4.htm You can change the red code block to do what you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Hello all I want to be able to rename a "Named Range" (Someone spelled it wrong) and now I have 1600+ workbooks that I need to change. This could be multiple places in the workbook itself. I was thinking that I could possibly do it with VB 6 or VB 2008. I was hoping that someone may have already had this issue and a quick solutions... If not then it's on to the coding... Thanks in advance. Rob |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
renaming a Named Range in multiple workbooks
Ron,
A question if I may. The named Range that I am looking to rename may also be used in calculations. Does this also handle this? Thank you Rob "Ron de Bruin" wrote: hi Tagwim See this page http://www.rondebruin.nl/copy4.htm You can change the red code block to do what you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Hello all I want to be able to rename a "Named Range" (Someone spelled it wrong) and now I have 1600+ workbooks that I need to change. This could be multiple places in the workbook itself. I was thinking that I could possibly do it with VB 6 or VB 2008. I was hoping that someone may have already had this issue and a quick solutions... If not then it's on to the coding... Thanks in advance. Rob |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
renaming a Named Range in multiple workbooks
Hi Tagwim
If you use something like this With mybook.Names("tax") .Name = "taxlow" End With And you have a formula like this =A4*tax It will change to =A4*taxlow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Ron, A question if I may. The named Range that I am looking to rename may also be used in calculations. Does this also handle this? Thank you Rob "Ron de Bruin" wrote: hi Tagwim See this page http://www.rondebruin.nl/copy4.htm You can change the red code block to do what you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Hello all I want to be able to rename a "Named Range" (Someone spelled it wrong) and now I have 1600+ workbooks that I need to change. This could be multiple places in the workbook itself. I was thinking that I could possibly do it with VB 6 or VB 2008. I was hoping that someone may have already had this issue and a quick solutions... If not then it's on to the coding... Thanks in advance. Rob |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
renaming a Named Range in multiple workbooks
Really? I tried your example and it didn't change the formula for me. I got
a #NAME? error instead. "Ron de Bruin" wrote: Hi Tagwim If you use something like this With mybook.Names("tax") .Name = "taxlow" End With And you have a formula like this =A4*tax It will change to =A4*taxlow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Ron, A question if I may. The named Range that I am looking to rename may also be used in calculations. Does this also handle this? Thank you Rob "Ron de Bruin" wrote: hi Tagwim See this page http://www.rondebruin.nl/copy4.htm You can change the red code block to do what you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Hello all I want to be able to rename a "Named Range" (Someone spelled it wrong) and now I have 1600+ workbooks that I need to change. This could be multiple places in the workbook itself. I was thinking that I could possibly do it with VB 6 or VB 2008. I was hoping that someone may have already had this issue and a quick solutions... If not then it's on to the coding... Thanks in advance. Rob |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
renaming a Named Range in multiple workbooks
Rob,
Unfortunately renaming a Name via VBA does not change the name in Formulae and any other place where the name is referenced. Name Manager (http://www.DecisionModels.com/downloads.htm) will handle the renaming in formulae and other places for a single workbook, but 1600 one at a time would be tough. Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Tagwim" wrote in message ... Ron, A question if I may. The named Range that I am looking to rename may also be used in calculations. Does this also handle this? Thank you Rob "Ron de Bruin" wrote: hi Tagwim See this page http://www.rondebruin.nl/copy4.htm You can change the red code block to do what you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Hello all I want to be able to rename a "Named Range" (Someone spelled it wrong) and now I have 1600+ workbooks that I need to change. This could be multiple places in the workbook itself. I was thinking that I could possibly do it with VB 6 or VB 2008. I was hoping that someone may have already had this issue and a quick solutions... If not then it's on to the coding... Thanks in advance. Rob |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
renaming a Named Range in multiple workbooks
Hi Charlie
Working OK here in 2007 where I test it but I see that in Excel 2003 it is different No time to test it now (tomorrow I will if you not have a answer) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Charlie" wrote in message ... Really? I tried your example and it didn't change the formula for me. I got a #NAME? error instead. "Ron de Bruin" wrote: Hi Tagwim If you use something like this With mybook.Names("tax") .Name = "taxlow" End With And you have a formula like this =A4*tax It will change to =A4*taxlow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Ron, A question if I may. The named Range that I am looking to rename may also be used in calculations. Does this also handle this? Thank you Rob "Ron de Bruin" wrote: hi Tagwim See this page http://www.rondebruin.nl/copy4.htm You can change the red code block to do what you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Hello all I want to be able to rename a "Named Range" (Someone spelled it wrong) and now I have 1600+ workbooks that I need to change. This could be multiple places in the workbook itself. I was thinking that I could possibly do it with VB 6 or VB 2008. I was hoping that someone may have already had this issue and a quick solutions... If not then it's on to the coding... Thanks in advance. Rob |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
renaming a Named Range in multiple workbooks
Hi Charles
It is working in Excel 2007 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Charles Williams" wrote in message ... Rob, Unfortunately renaming a Name via VBA does not change the name in Formulae and any other place where the name is referenced. Name Manager (http://www.DecisionModels.com/downloads.htm) will handle the renaming in formulae and other places for a single workbook, but 1600 one at a time would be tough. Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Tagwim" wrote in message ... Ron, A question if I may. The named Range that I am looking to rename may also be used in calculations. Does this also handle this? Thank you Rob "Ron de Bruin" wrote: hi Tagwim See this page http://www.rondebruin.nl/copy4.htm You can change the red code block to do what you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Hello all I want to be able to rename a "Named Range" (Someone spelled it wrong) and now I have 1600+ workbooks that I need to change. This could be multiple places in the workbook itself. I was thinking that I could possibly do it with VB 6 or VB 2008. I was hoping that someone may have already had this issue and a quick solutions... If not then it's on to the coding... Thanks in advance. Rob |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
renaming a Named Range in multiple workbooks
Ron,
Thank you it did the trick. Excellent information Thank you again Rob "Ron de Bruin" wrote: Hi Tagwim If you use something like this With mybook.Names("tax") .Name = "taxlow" End With And you have a formula like this =A4*tax It will change to =A4*taxlow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Ron, A question if I may. The named Range that I am looking to rename may also be used in calculations. Does this also handle this? Thank you Rob "Ron de Bruin" wrote: hi Tagwim See this page http://www.rondebruin.nl/copy4.htm You can change the red code block to do what you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Hello all I want to be able to rename a "Named Range" (Someone spelled it wrong) and now I have 1600+ workbooks that I need to change. This could be multiple places in the workbook itself. I was thinking that I could possibly do it with VB 6 or VB 2008. I was hoping that someone may have already had this issue and a quick solutions... If not then it's on to the coding... Thanks in advance. Rob |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
renaming a Named Range in multiple workbooks
Hi Ron,
Thanks, I did not know that (although I knew the native 2007 Msoft Name manager can do it). Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Ron de Bruin" wrote in message ... Hi Charles It is working in Excel 2007 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Charles Williams" wrote in message ... Rob, Unfortunately renaming a Name via VBA does not change the name in Formulae and any other place where the name is referenced. Name Manager (http://www.DecisionModels.com/downloads.htm) will handle the renaming in formulae and other places for a single workbook, but 1600 one at a time would be tough. Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Tagwim" wrote in message ... Ron, A question if I may. The named Range that I am looking to rename may also be used in calculations. Does this also handle this? Thank you Rob "Ron de Bruin" wrote: hi Tagwim See this page http://www.rondebruin.nl/copy4.htm You can change the red code block to do what you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tagwim" wrote in message ... Hello all I want to be able to rename a "Named Range" (Someone spelled it wrong) and now I have 1600+ workbooks that I need to change. This could be multiple places in the workbook itself. I was thinking that I could possibly do it with VB 6 or VB 2008. I was hoping that someone may have already had this issue and a quick solutions... If not then it's on to the coding... Thanks in advance. Rob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Named Range: Same Names, Multiple Workbooks with Same Sheet Name | Excel Discussion (Misc queries) | |||
| renaming a named range | Excel Discussion (Misc queries) | |||
| Access Named range value with multiple workbooks open | Excel Programming | |||
| Strange named range issue with multiple workbooks | Excel Discussion (Misc queries) | |||
| How do you point to a named range in linked workbooks? | Excel Discussion (Misc queries) | |||
Linear Mode
