I have a set of workbooks with names of the form
Dataset_UV01_21_3327.xls and the individual worksheets are called
UV??_??_????_0

....

UV??_??_????_8

I wish to write a macro that will rename each sheet

sheet0 ... sheet8

So that other macros I write which move data from sheet to sheet will
work in any workbook.

I started with:

Worksheets("UV??_??_????_0").Name = "0"
Worksheets("UV??_??_????_1").Select
Worksheets("UV??_??_????_1").Name = "1"
Worksheets("UV??_??_????_2").Select
Worksheets("UV??_??_????_2").Name = "2"
Worksheets("UV??_??_????_3").Select
Worksheets("UV??_??_????_3").Name = "3"
...

And got Run-time error '9' Subscript out of range

I then tried like statements e.g.

If Sheet.Name Like "UV??_??_????_3" Then
Sheet.Name = "3"
End If

And got Run-time error '424': Object required

But I don't know how to fix these errors.

thanks in advance

Mike Cushman

The errors were caused because you cannot use wildcards like that.

Worksheets("UV??_??_????_0") would be looking for a worksheet with the
name UV??_??_????_0 which doesn't exist.

Not sure why the like didn't work...

Here's code to do what you want:

Sub renameum()

Dim s As Worksheet

For Each s In Worksheets
If InStrRev(s.Name, "_") 0 Then
s.Name = "Sheet" & Right(s.Name, Len(s.Name) - InStrRev(s.Name,
"_"))
End If
Next

End Sub

K


--
kkknie
------------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...fo&userid=7543
View this thread: http://www.excelforum.com/showthread...hreadid=265554

As long as the sheet numbers stay single digit (i.e. 0-9) then how about
looping through the set

For Each Sheet In Sheets
Sheet.Name = Right((Sheet.Name), 1)
Next Sheet

If you have other sheets in the set you don't want renamed, then an If
statement could be used to check for conformance
Paul D

"btb_London" wrote in message
om...
I have a set of workbooks with names of the form
Dataset_UV01_21_3327.xls and the individual worksheets are called
UV??_??_????_0

...

UV??_??_????_8

I wish to write a macro that will rename each sheet

sheet0 ... sheet8

So that other macros I write which move data from sheet to sheet will
work in any workbook.

I started with:

Worksheets("UV??_??_????_0").Name = "0"
Worksheets("UV??_??_????_1").Select
Worksheets("UV??_??_????_1").Name = "1"
Worksheets("UV??_??_????_2").Select
Worksheets("UV??_??_????_2").Name = "2"
Worksheets("UV??_??_????_3").Select
Worksheets("UV??_??_????_3").Name = "3"
...

And got Run-time error '9' Subscript out of range

I then tried like statements e.g.

If Sheet.Name Like "UV??_??_????_3" Then
Sheet.Name = "3"
End If

And got Run-time error '424': Object required

But I don't know how to fix these errors.

thanks in advance

Mike Cushman

Define objects first.

Option Explicit
Public Sub renSheets()
Dim wb As Workbook, x, y As Integer
Set wb = ThisWorkbook
x = wb.Sheets.Count
For y = 1 To x Step 1
MsgBox wb.Sheets(y).Name
Next
End Sub


"btb_London" schreef in bericht
om...
I have a set of workbooks with names of the form
Dataset_UV01_21_3327.xls and the individual worksheets are called
UV??_??_????_0

...

UV??_??_????_8

I wish to write a macro that will rename each sheet

sheet0 ... sheet8

So that other macros I write which move data from sheet to sheet will
work in any workbook.

I started with:

Worksheets("UV??_??_????_0").Name = "0"
Worksheets("UV??_??_????_1").Select
Worksheets("UV??_??_????_1").Name = "1"
Worksheets("UV??_??_????_2").Select
Worksheets("UV??_??_????_2").Name = "2"
Worksheets("UV??_??_????_3").Select
Worksheets("UV??_??_????_3").Name = "3"
...

And got Run-time error '9' Subscript out of range

I then tried like statements e.g.

If Sheet.Name Like "UV??_??_????_3" Then
Sheet.Name = "3"
End If

And got Run-time error '424': Object required

But I don't know how to fix these errors.

thanks in advance

Mike Cushman

Thanks, this worked a dream (once I'd got rid of the line break!)

Mike

kkknie wrote in message ...
The errors were caused because you cannot use wildcards like that.

Worksheets("UV??_??_????_0") would be looking for a worksheet with the
name UV??_??_????_0 which doesn't exist.

Not sure why the like didn't work...

Here's code to do what you want:

Sub renameum()

Dim s As Worksheet

For Each s In Worksheets
If InStrRev(s.Name, "_") 0 Then
s.Name = "Sheet" & Right(s.Name, Len(s.Name) - InStrRev(s.Name,
"_"))
End If
Next

End Sub

K