how can you re-number multiple sheets 'automatically'?

M

Mihaela

I have a large worksheet with a large number (100+) of sheets that I would
like to rename/renumber and don't want to do it one-by-one. I.e. change from
Sheet 1, Sheet 2, etc to just 1,2,3,....
 
R

Rowan

If your sheets are named Sheet1, Sheet2 etc without the spaces shown in your
post then you can use a macro like this to change the names:

Sub ChangeNames()
Dim i As Integer
For i = 1 To Sheets.Count
If Left(Sheets(i).Name, 5) = "Sheet" Then
Sheets(i).Name = Mid(Sheets(i).Name, 6 _
, Len(Sheets(i).Name) - 5)
End If
Next i
End Sub

This will fail if you have a sheet name "Sheet1" and aonther sheet already
named "1", as it will try to rename "Sheet1" as "1".

Hope this helps
Rowan
 
Top