Rename worksheets based on cell values with 'illegal' character (/)

Dear Experts:

I need to rename all worksheets of the active workbook (with the exception of one worksheet named 'DataSource') ...
... based on the cell value B2 of the respective worksheets

The trouble is that these cell values in B2 all contain one illegal character, ie. a forward slash '/' such as F/123 or G/497

Is it possible to replace this illegal character with an 'underscore' character, so that the worksheets name have names like
... F_123, G_497, T_587 etc.

The cell values have to be left alone, they should retain the forward slash.

Could somebody please come up with such a VBA code.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Few Questions??

Are these sheets are created or you want to create them basis of Cell value?
If you want to create them then what's the logic and how do you relate which column cell value is equal to sheet name??
If they are already created then how do you map the sheet name to cell value?? as in which sheet name is equal to which cell value?

Saurabh...
0
ProfessorJimJamCommented:
AndreasHermle

please find attach.  open the attachment and put your sheet names in the column A and then click that button.

then all of the worksheets will be created, those ones with illegal char will be ignored.
EE.xlsm
0
Andreas HermleTeam leaderAuthor Commented:
Dear Saurabh,

they are all already created with the default sheet names, i.e. sheet 1, sheet 2, sheet 3, etc.

B2 value of sheet 1 is for example: F/347. After renaming the sheet name is: F_347
B2 value of sheet 2 is for example: G/454. After renaming the sheet name is: G_454
B2 value of sheet 3 is for example: R/789. After renaming the sheet name is: R_789

One worksheet name 'DataSource' is not to be renamed.

I hope I could make myself clear. Thank you very much for your professional efforts and the time taken.

Regards, Andreas
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

ProfessorJimJamCommented:
correct attachment.
EE.xlsm
0
Andreas HermleTeam leaderAuthor Commented:
Hi Prof JimJam,

thank you very much for your swift support. The macro works great and I am sure that this macro will come in handy some other time.

I am afraid I should have make myself clearer, see my comment to Saurabh above, i.e. the sheets are all already created and need to be renamed based on the respective B2 cell value (replacing the / character with the _ (underscore) character)

Help is much appreciated. Thank you very much in advance. Regards, Andreas
0
Andreas HermleTeam leaderAuthor Commented:
I'd better attach a sample file. Here it is ...Thank you
renaming-worksheets-based-on-B2-cell-val
0
ProfessorJimJamCommented:
AndreasHermle

please find attached, this is automatic and every time you change b2 cell in any sheet with any value. it will do the job.

the attachment is the example. if you want this to be done in your workbook. simply copy the below code and put it in the "ThisWorkbook" object

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  
     Dim sNewName As String
     Dim sMsg As String, sEndMsg As String
     Dim sTitle As String
     
     Const sDATEFORM As String = "yyyymmdd"
    Const sNUMFORM As String = "#0.00?"
     
     'Set the title for the message box
    sTitle = "Invalid Sheet Name"
 
     'Make sure it's the cell we want
    If Target.Address = "$B$2" Then
         
         'Account for specific data in the cell
        If IsDate(Target.Value) Then
             sNewName = Format(Target.Value, sDATEFORM)
         ElseIf IsNumeric(Target.Value) Then
             sNewName = Format(Target.Value, sNUMFORM)
         Else
             'sNewName = Target.Value
            'thanks, Charlie
             sNewName = CStr(Target.Value)
         End If
         
         'Get rid of illegal or unwanted characters
        sNewName = CleanSheetName(sNewName)
         
         'Create the end of the prompt for the message box
        sEndMsg = vbNewLine & vbNewLine & "The sheet name will not be changed." & _
             vbNewLine & vbNewLine & "Sheet name attempted: " & sNewName
             
         'Establish error checking
        On Error Resume Next
             'Attempt to rename the sheet
            Sh.Name = sNewName
             
             'If there's an error
            If Err.Number <> 0 Then
                 
                 'Be more descriptive for a certain error, otherwise
                'return the error that Excel returns
                If Left(Err.Description, 19) = "Application-defined" Then
                     sMsg = "You entered an invalid sheet name." & sEndMsg
                 Else
                     sMsg = Err.Description & sEndMsg
                 End If
                 
                 'Display the error
                MsgBox sMsg, vbOKOnly, sTitle
                 
             End If
         On Error GoTo 0
     End If
     
End Sub

Public Function CleanSheetName(ByVal sOldName As String, _
     Optional sReplacement As String = "_") As String
  
     Dim vaIllegal As Variant
     Dim i As Long
     Dim sTemp As String
     
     sTemp = sOldName
     'List unwanted characters
    vaIllegal = Array(".", "?", "!", "*", "/", "", "[", "]", "'")
     
     'Make sure replacement isn't illegal
    For i = LBound(vaIllegal) To UBound(vaIllegal)
         If sReplacement = vaIllegal(i) Then
             sReplacement = "_"
            Exit For
         End If
     Next i

    'Replace all illegals with the replacement
    For i = LBound(vaIllegal) To UBound(vaIllegal)
         sTemp = Replace(sTemp, vaIllegal(i), sReplacement)
     Next i
     
     CleanSheetName = sTemp
     
End Function

Open in new window

EE.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
You can use this code which will do what you are looking for...

Sub rename()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        If ws.Name <> "DataSource" Then ws.Name = Replace(ws.Range("B2").Value, "/", "_")

    Next ws
End Sub

Open in new window


Saurabh...
0
Andreas HermleTeam leaderAuthor Commented:
Hi Prof JimJam,

thank you very much for this superb code, I am really impressed. There is one thing I would like to inquire.

I got more than one hundred worksheets in my workbook which need to be renamed.

For the first renaming, is there a possibility to do this first renaming in one go. To be honest with you, I do not want to activate each and every sheet and retype the B2 value to trigger the first renaming.

And moreover there is one sheet called 'DataSource' where no renaming should occur.

Again, your code is fantastic, thank you very much for it.
Help is much appreciated. Thank you very much in advance. Regards, Andreas
0
Saurabh Singh TeotiaCommented:
Andres,

Try the code which i gave will do what you are looking for..

Saurabh...
0
Andreas HermleTeam leaderAuthor Commented:
Hi Saurabh,

great this did the trick. Thank you very much for it.

So ProfJimJam, the first renaming is done by Saurabh's code and then subsequent ones can be achieved automatically by your code. So the only thing than remains to be done that the automatic renaming should exclude the DataSource worksheet.

Thank you very much to both of you, Regards, Andreas
0
ProfessorJimJamCommented:
AndreasHermle

the DataSource will not be affected unless you make changes into that name meaning if you change the DataSource to any other name.

also Saurabh code does not affect the DataSource as well, it will bypass this name.

one more thing.  Saurabh's code will not replace other illegal characters like ? or / or ] etc   while my code will take care of those as well.  
however it is easy to modify Saurabh's code to incorporate those addtional chars as well.
0
Saurabh Singh TeotiaCommented:
You need to make small changes in Jim code which is this will do what you are looking for..

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  
     Dim sNewName As String
     Dim sMsg As String, sEndMsg As String
     Dim sTitle As String
     
     Const sDATEFORM As String = "yyyymmdd"
    Const sNUMFORM As String = "#0.00?"
     
     If Sh.Name <> "DataSource" Then
     'Set the title for the message box
    sTitle = "Invalid Sheet Name"
 
     'Make sure it's the cell we want
    If Target.Address = "$B$2" Then
         
         'Account for specific data in the cell
        If IsDate(Target.Value) Then
             sNewName = Format(Target.Value, sDATEFORM)
         ElseIf IsNumeric(Target.Value) Then
             sNewName = Format(Target.Value, sNUMFORM)
         Else
             'sNewName = Target.Value
            'thanks, Charlie
             sNewName = CStr(Target.Value)
         End If
         
         'Get rid of illegal or unwanted characters
        sNewName = CleanSheetName(sNewName)
         
         'Create the end of the prompt for the message box
        sEndMsg = vbNewLine & vbNewLine & "The sheet name will not be changed." & _
             vbNewLine & vbNewLine & "Sheet name attempted: " & sNewName
             
         'Establish error checking
        On Error Resume Next
             'Attempt to rename the sheet
            Sh.Name = sNewName
             
             'If there's an error
            If Err.Number <> 0 Then
                 
                 'Be more descriptive for a certain error, otherwise
                'return the error that Excel returns
                If Left(Err.Description, 19) = "Application-defined" Then
                     sMsg = "You entered an invalid sheet name." & sEndMsg
                 Else
                     sMsg = Err.Description & sEndMsg
                 End If
                 
                 'Display the error
                MsgBox sMsg, vbOKOnly, sTitle
                 
             End If
         On Error GoTo 0
     End If
     End If
     
End Sub

Public Function CleanSheetName(ByVal sOldName As String, _
     Optional sReplacement As String = "_") As String
  
     Dim vaIllegal As Variant
     Dim i As Long
     Dim sTemp As String
     
     sTemp = sOldName
     'List unwanted characters
    vaIllegal = Array(".", "?", "!", "*", "/", "", "[", "]", "'")
     
     'Make sure replacement isn't illegal
    For i = LBound(vaIllegal) To UBound(vaIllegal)
         If sReplacement = vaIllegal(i) Then
             sReplacement = "_"
            Exit For
         End If
     Next i

    'Replace all illegals with the replacement
    For i = LBound(vaIllegal) To UBound(vaIllegal)
         sTemp = Replace(sTemp, vaIllegal(i), sReplacement)
     Next i
     
     CleanSheetName = sTemp
     
End Function

Open in new window

0
ProfessorJimJamCommented:
thank you Saurabh
0
Saurabh Singh TeotiaCommented:
:-) .. Anytime Jim..so i guess i got your company right now.. :-)
0
ProfessorJimJamCommented:
Yes Saurabh :-)
0
Andreas HermleTeam leaderAuthor Commented:
great coding, two different approaches which I can use both. I am really impressed with your VBA expertise. Thank you very much for your great and professional support. I really appreciate it. Regards, Andreas
0
ProfessorJimJamCommented:
thank you Andreas for nice feedback.
0
Saurabh Singh TeotiaCommented:
Yw..Always Happy to Help.. :-)

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.