|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 11:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com