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