Macro to convert Name of worksheet

I want to change the name of all the worksheets by using a table in another
worksheet. The table contains one column for long names and another column
for short names. When the current worksheet name matches the long name
column, the name is change to the short name.

Thanks,
G

Dim iPos As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
iPos = Application.Match(wks.Name, Range("LongNames"), 0)
On Error GoTo 0
If iPos 0 Then
wks.Name = Range("ShortNames").Cells(iPos, 1)
End If
Next wks


--

HTH

RP
(remove nothere from the email address if mailing direct)


"G" wrote in message
...
I want to change the name of all the worksheets by using a table in
another
worksheet. The table contains one column for long names and another
column
for short names. When the current worksheet name matches the long name
column, the name is change to the short name.

Thanks,
G

Cool Bob

And I even understand it!!g


Gord

On Mon, 21 Nov 2005 18:56:56 -0000, "Bob Phillips"
wrote:


Dim iPos As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
iPos = Application.Match(wks.Name, Range("LongNames"), 0)
On Error GoTo 0
If iPos 0 Then
wks.Name = Range("ShortNames").Cells(iPos, 1)
End If
Next wks

Thanks Gord.

Bob


"Gord Dibben" gorddibbATshawDOTca wrote in message
...
Cool Bob

And I even understand it!!g


Gord

On Mon, 21 Nov 2005 18:56:56 -0000, "Bob Phillips"
wrote:


Dim iPos As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
iPos = Application.Match(wks.Name, Range("LongNames"), 0)
On Error GoTo 0
If iPos 0 Then
wks.Name = Range("ShortNames").Cells(iPos, 1)
End If
Next wks

I used both your original example and the modified version below. Neither
seems to run. I get no error msg or any results. I understand what the code
is trying to do, I'm I forgetting something? Thanks again!

Sub Rename()

Dim iPos As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
iPos = Application.Match(ws.Name, Range("BrokerInfo!a2:a84"), 0)
On Error GoTo 0
If iPos 0 Then
ws.Name = Range("BrokerInfo!b2:b84").Cells(iPos, 1)
End If
Next wks
End Sub



"Bob Phillips" wrote:


Dim iPos As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
iPos = Application.Match(wks.Name, Range("LongNames"), 0)
On Error GoTo 0
If iPos 0 Then
wks.Name = Range("ShortNames").Cells(iPos, 1)
End If
Next wks


--

HTH

RP
(remove nothere from the email address if mailing direct)


"G" wrote in message
...
I want to change the name of all the worksheets by using a table in
another
worksheet. The table contains one column for long names and another
column
for short names. When the current worksheet name matches the long name
column, the name is change to the short name.

Thanks,
G

It works (just needed to change ws.name to wks.name since it's wks after
dim). Thanks for the help!!!!

"G" wrote:

I used both your original example and the modified version below. Neither
seems to run. I get no error msg or any results. I understand what the code
is trying to do, I'm I forgetting something? Thanks again!

Sub Rename()

Dim iPos As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
iPos = Application.Match(ws.Name, Range("BrokerInfo!a2:a84"), 0)
On Error GoTo 0
If iPos 0 Then
ws.Name = Range("BrokerInfo!b2:b84").Cells(iPos, 1)
End If
Next wks
End Sub



"Bob Phillips" wrote:


Dim iPos As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
iPos = Application.Match(wks.Name, Range("LongNames"), 0)
On Error GoTo 0
If iPos 0 Then
wks.Name = Range("ShortNames").Cells(iPos, 1)
End If
Next wks


--

HTH

RP
(remove nothere from the email address if mailing direct)


"G" wrote in message
...
I want to change the name of all the worksheets by using a table in
another
worksheet. The table contains one column for long names and another
column
for short names. When the current worksheet name matches the long name
column, the name is change to the short name.

Thanks,
G

Well spotted. And you did it yourself, which is a better all round :-))

Bob


"G" wrote in message
...
It works (just needed to change ws.name to wks.name since it's wks after
dim). Thanks for the help!!!!

"G" wrote:

I used both your original example and the modified version below.
Neither
seems to run. I get no error msg or any results. I understand what the
code
is trying to do, I'm I forgetting something? Thanks again!

Sub Rename()

Dim iPos As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
iPos = Application.Match(ws.Name, Range("BrokerInfo!a2:a84"), 0)
On Error GoTo 0
If iPos 0 Then
ws.Name = Range("BrokerInfo!b2:b84").Cells(iPos, 1)
End If
Next wks
End Sub



"Bob Phillips" wrote:


Dim iPos As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
iPos = Application.Match(wks.Name, Range("LongNames"), 0)
On Error GoTo 0
If iPos 0 Then
wks.Name = Range("ShortNames").Cells(iPos, 1)
End If
Next wks


--

HTH

RP
(remove nothere from the email address if mailing direct)


"G" wrote in message
...
I want to change the name of all the worksheets by using a table in
another
worksheet. The table contains one column for long names and another
column
for short names. When the current worksheet name matches the long
name
column, the name is change to the short name.

Thanks,
G

It appears to me that you have not properly named the ranges as Bob's code
uses range names "Longnames" and "Shortnames"

On BrokerInfo sheet select A2:A84 and InsertNameDefine

Name this range "Longnames"(no quotes)

Select B2:B84 and do the same for "Shortnames".

Now run Bob's macro.

On your code "BrokerInfo!a2:a84" doesn't appear to be a named range.

Looks like a range address which VBA will not accept.

You also have a couple of typos.......

change both instances of ws.name to wks.name


Gord Dibben Excel MVP

On Tue, 22 Nov 2005 09:40:20 -0800, "G" wrote:

I used both your original example and the modified version below. Neither
seems to run. I get no error msg or any results. I understand what the code
is trying to do, I'm I forgetting something? Thanks again!

Sub Rename()

Dim iPos As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
iPos = Application.Match(ws.Name, Range("BrokerInfo!a2:a84"), 0)
On Error GoTo 0
If iPos 0 Then
ws.Name = Range("BrokerInfo!b2:b84").Cells(iPos, 1)
End If
Next wks
End Sub



"Bob Phillips" wrote:


Dim iPos As Long
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
iPos = Application.Match(wks.Name, Range("LongNames"), 0)
On Error GoTo 0
If iPos 0 Then
wks.Name = Range("ShortNames").Cells(iPos, 1)
End If
Next wks


--

HTH

RP
(remove nothere from the email address if mailing direct)


"G" wrote in message
...
I want to change the name of all the worksheets by using a table in
another
worksheet. The table contains one column for long names and another
column
for short names. When the current worksheet name matches the long name
column, the name is change to the short name.

Thanks,
G