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

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

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