Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 --- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 --- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 --- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to strip away "Sheet" prefix from names
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How to prefix "2" to every cell in a column | Excel Discussion (Misc queries) | |||
Please add a "sheet" function like "row" and "column" functions | Excel Programming | |||
"Strip" Cell Formula | Excel Programming | |||
Backup to specific folder if workbook names begins with "NSR" or "MAC" | Excel Programming |