Rename worksheets based on cell values - defining characters that need to be replaced

Dear Experts:

below code renames worksheets based on values in A2, C2 and D2 of the respective worksheet.

It runs just fine with one exception. If one of the above cells contains a slash (/) or a back slash (\) the macro does not rename that worksheet, ie. it gets skipped in the process.

I do not understand why since I listed these characters (Slash and Back Slash) as characters that will be replaced with an underscore (_), see line 18 and 21 of the below macro.

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

Regards, Andreas


Sub Rename_Worksheets()
'replacing certain characters
'renaming worksheets based on cell values

 If MsgBox("Would you like to rename worksheets based on values in A2, C2 as well as D2?" & vbCrLf & vbCrLf & _
    "The macro may require adjustment with regard to the specific cell references", vbQuestion + vbYesNo, "Rename worksheets based on cell values") = vbNo Then
        Exit Sub
        End If


Dim ws As Worksheet
Dim v As Variant
For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next
    If Len(ws.Range("A2")) > 0 And Len(ws.Range("C2")) > 0 And Len(ws.Range("D2")) > 0 Then
           ws.Name = Left(ws.Range("A2").Value, 2) & "_" & Left(ws.Range("C2"), 2) & "_" & Left(ws.Range("D2").Value, 19)

                For Each v In Array("&", ",", ".", ")", "(", "/", "\", "|", ":", "*", "?", "<", ">", """")
                    'It skips the ones with slash and back slash. Why?

                    ws.Name = Replace(ws.Name, v, "_")

                Next
    End If

Next
End Sub

Open in new window

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.

Rgonzo1971Commented:
Hi,

the chars are invalid in sheet name
 [ ] * ? / \ .
then try

strName = Left(ws.Range("A2").Value, 2) & "_" & Left(ws.Range("C2"), 2) & "_" & Left(ws.Range("D2").Value, 19)
For Each v In Array("&", ",", ".", ")", "(", "/", "\", "|", ":", "*", "?", "<", ">", """")
    strName = Replace(strName, v, "_")
Next
ws.Name = strName

Open in new window

Regards
0
Andreas HermleTeam leaderAuthor Commented:
Hi Rgonzo,

thank you very much for your great help. I am afraid to tell you that if I incorporate your snippet into my macro, the macro does no do anything.

Any idea why? I could also always make sure that the cells that serve as base for the worksheet names do not contain these special characters [ ], * ? / \

Regards, Andreas
0
Rgonzo1971Commented:
Hi,

see example

Sub Rename_Worksheets()
'replacing certain characters
'renaming worksheets based on cell values

 If MsgBox("Would you like to rename worksheets based on values in A2, C2 as well as D2?" & vbCrLf & vbCrLf & _
    "The macro may require adjustment with regard to the specific cell references", vbQuestion + vbYesNo, "Rename worksheets based on cell values") = vbNo Then
        Exit Sub
        End If


Dim ws As Worksheet
Dim v As Variant
For Each ws In ActiveWorkbook.Worksheets

    If Len(ws.Range("A2")) > 0 And Len(ws.Range("C2")) > 0 And Len(ws.Range("D2")) > 0 Then
        strName = Left(ws.Range("A2").Value, 2) & "_" & Left(ws.Range("C2"), 2) & "_" & Left(ws.Range("D2").Value, 19)
        For Each v In Array("&", ",", ".", ")", "(", "/", "\", "|", ":", "*", "?", "<", ">", """")
            strName = Replace(strName, v, "_")
        Next
        ws.Name = strName
    End If

Next
End Sub

Open in new window

EE20150903.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Andreas HermleTeam leaderAuthor Commented:
Wow, Rgonzo, great, this works just fine, thank you very much for your superb help :-) :-)

We are almost there, there is one thing I would like you to look into:

I changed line 17 to 'For Each v In Array("&", ",", ".", ")", "(", "/", "\", "|", ":", "*", "?", "<", ">", , " ", """")' ,that is, I added " " to the array, i.e. that spaces are also replaced with an underscore.

After running the code some worksheet names contain several underscores (in a row) because of the make-up of the array, such as 3_IN_X_Corp____F. Is it possible to replace several consecutive underscores with just one underscore

Thank you, Regards, Andreas
0
Rgonzo1971Commented:
then try

Sub Rename_Worksheets()
'replacing certain characters
'renaming worksheets based on cell values

 If MsgBox("Would you like to rename worksheets based on values in A2, C2 as well as D2?" & vbCrLf & vbCrLf & _
    "The macro may require adjustment with regard to the specific cell references", vbQuestion + vbYesNo, "Rename worksheets based on cell values") = vbNo Then
        Exit Sub
        End If


Dim ws As Worksheet
Dim v As Variant
For Each ws In ActiveWorkbook.Worksheets

    If Len(ws.Range("A2")) > 0 And Len(ws.Range("C2")) > 0 And Len(ws.Range("D2")) > 0 Then
        'ws.Name = Left(ws.Range("A2").Value, 2) & "_" & Left(ws.Range("C2"), 2) & "_" & Left(ws.Range("D2").Value, 19)

        strName = Left(ws.Range("A2").Value, 2) & "_" & Left(ws.Range("C2"), 2) & "_" & Left(ws.Range("D2").Value, 19)
        For Each v In Array("&", ",", ".", ")", "(", "/", "\", "|", ":", "*", "?", "<", ">", " ", """")
            strName = Replace(strName, v, "_")
        Next
        Do While InStr(1, strName, "__", vbTextCompare)
            strName = Replace(strName, "__", "_")
        Loop
        ws.Name = strName
    End If

Next
End Sub

Open in new window

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
Andreas HermleTeam leaderAuthor Commented:
Wow, Rgonzo, I am really impressed. Works as desired. Thank you very much for your professional and swift support.
Regards, Andreas
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.