Rename Several Defined Range Names with Macro
thanks Tom
I appreciate what you gave me
on another note
If I wanted to get started learning VBA code
where do you recommend I start from
I'm getting to the point where I feel like I should know how to do
these things...
I'm pretty good in Excel at least everybody I work with comes to me
with Excel questions
Where does one start?
Tom Ogilvy wrote:
Try something along these lines:
Sub ChangeNames()
Dim nm as Name
Dim s as string, s1 as String, s2 as string
for each nm in thisworkbook.names
s = nm.name
s1 = right(s,4)
if s1 like "06##" then
s2 = "07" & right(s1,2)
s1 = left(s,len(s)-4) & s2
application.Range(s).Name = s1
nm.Delete
end if
Next
End sub
The code worked for me.
--
Regards,
Tom Ogilvy
"ExcelUser777" wrote in message
oups.com...
Hi
I have an Excel Workbook with over 200 range names and I would like to
programatically change each range name. I'm trying to avoid hunting
down each range name and changing the range name manually.
What I'm trying to accomplish:
Change range name BS_36S_0601 to BS_36S_0701 for all 12 periods for
each company
BS = Balance Sheet
IS = Income Statement
*****The original range covered will not be adjusted just the range
name.
For example
I have the following range names: I have set ranges for the 12
periods of the year and I also in addition to 36S,
(substitute BS for 36S ) I have BS, UM, UC, MG, JLK, SH and several
others...
BS_36S_0601 to 0701
BS_36S_0602 to 0702
BS_36S_0604 to 0703
BS_36S_0605
BS_36S_0606
BS_36S_0607
BS_36S_0608
BS_36S_0609
BS_36S_0610
BS_36S_0611
BS_36S_0612
IS_36S_0601
IS_36S_0602
IS_36S_0604
IS_36S_0605
IS_36S_0606
IS_36S_0607
IS_36S_0608
IS_36S_0609
IS_36S_0610
IS_36S_0611
IS_36S_0612
Thanks,
lax_777
|