Sub to strip away "Sheet" prefix from names
 

Looking for a sub which can strip away the "Sheet" prefix from all sheets
bearing such names, eg sheets named as: Sheet2, Sheet3, Sheet4 ... will be
renamed as simply: 2,3,4 ... Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sub to strip away "Sheet" prefix from names
 

Hi Max,

Try something like:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE

For Each SH In WB.Worksheets
With SH
If UCase(.Name) Like "SHEET*" Then
.Name = Replace(.Name, "Sheet", _
vbNullString, 1, 1, vbTextCompare)
End If
End With
Next SH
End Sub
'<<=============


---
Regards,
Norman


"Max" wrote in message
...
Looking for a sub which can strip away the "Sheet" prefix from all sheets
bearing such names, eg sheets named as: Sheet2, Sheet3, Sheet4 ... will be
renamed as simply: 2,3,4 ... Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sub to strip away "Sheet" prefix from names
 

Norman,
Many thanks. Runs great !

Set WB = Workbooks("Test1a.xls") '<<==== CHANGE
Just one question, how could your sub be generalized
to run on all books open simultaneously in the same Excel session ?
(hopefully the filenames need not be specified)

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sub to strip away "Sheet" prefix from names
 

Hi Max,

'------------------
Set WB = Workbooks("Test1a.xls") '<<==== CHANGE
Just one question, how could your sub be generalized
to run on all books open simultaneously in the same Excel session ?
(hopefully the filenames need not be specified)
'------------------

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet

For Each WB In Application.Workbooks
For Each SH In WB.Worksheets
With SH
If UCase(.Name) Like "SHEET*" Then
.Name = Replace(.Name, "Sheet", _
vbNullString, 1, 1, vbTextCompare)
End If
End With
Next SH
Next WB
End Sub
'<<=============



---
Regards,
Norman

Sub to strip away "Sheet" prefix from names
 

Marvellous, Norman !
Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---