Renaming an Excel sheet from a cell that has special character

Question

I have created macro code that copies the value of the value in "L2" which works but my problem is if the value of "L2" has a special character it gives me an error. I know the rule that special character is not allowed in renaming a sheet.

Is there a way to bypassed it? say it copies the text from (L2) except the special character? Thank you.

Sub Test()

    Range("L2").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = Range("L2")

Below is the code I have that launched tables from the "pivot" sheet and need to rename them based from the value in Range ("L2").

    Range("B2").Select
    Selection.ShowDetail = True
    Cells.Select
    Selection.RowHeight = 15
    Range("L2").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = Range("L2")
    Sheets("pivot").Select
    Range("B3").Select
    Selection.ShowDetail = True
    Cells.Select
    Selection.RowHeight = 15
    Range("L2").Select
    Selection.Copy
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = Range("L2")
    Sheets("pivot").Select
    Range("B4").Select
    Selection.ShowDetail = True
    Cells.Select
    Selection.RowHeight = 15
    Range("L2").Select
    Selection.Copy
    Sheets("Sheet5").Select
    Sheets("Sheet5").Name = Range("L2")
    Sheets("pivot").Select
    Range("B5").Select
    Selection.ShowDetail = True
    Cells.Select
    Selection.RowHeight = 15
    Range("L2").Select
    Selection.Copy
    Sheets("Sheet6").Select
    Sheets("Sheet6").Name = Range("L2")
    Sheets("pivot").Select
    Range("B6").Select
    Selection.ShowDetail = True
    Cells.Select
    Selection.RowHeight = 15
    Range("L2").Select
    Selection.Copy
    Sheets("Sheet7").Select
    Sheets("Sheet7").Name = Range("L2")
    Sheets("pivot").Select
    Range("B7").Select
    Selection.ShowDetail = True
    Cells.Select
    Selection.RowHeight = 15
    Range("L2").Select
    Selection.Copy
    Sheets("Sheet9").Select
    Sheets("Sheet9").Name = Range("L2")
    Sheets("pivot").Select
    Range("B8").Select
    Selection.ShowDetail = True
    Cells.Select
    Selection.RowHeight = 15
    Range("L2").Select
    Selection.Copy
    Sheets("Sheet10").Select
    Sheets("Sheet10").Name = Range("L2")
    Sheets("pivot").Select
    Range("B9").Select
    Selection.ShowDetail = True
    Cells.Select
    Selection.RowHeight = 15
    Range("L2").Select
    Selection.Copy
    Sheets("Sheet11").Select
    Sheets("Sheet11").Name = Range("L2")
    Sheets("pivot").Select
    Range("B10").Select
    Selection.ShowDetail = True
    Cells.Select
    Selection.RowHeight = 15
    Range("L2").Select
    Selection.Copy
    Sheets("Sheet12").Select
    Sheets("Sheet12").Name = Range("L2")
    Sheets("pivot").Select
    Range("B11").Select
    Selection.ShowDetail = True
    Cells.Select
    Selection.RowHeight = 15
    Range("L2").Select
    Selection.Copy
    Sheets("Sheet13").Select
    Sheets("Sheet13").Name = Range("L2")
    Range("L2").Select
End Sub

This problem is really similar to this question: https://stackoverflow.com/questions/24356993/removing-special-characters-vba-excel — DecimalTurn, Nov 02 '17 at 03:08
you really need to get rid of all the `select` statements. ... your code will probably reduce to less than 30 lines — jsotola, Nov 02 '17 at 03:18
I removed the select statements now and code looks more cleaner thanks to the advised @jsotola. — Jonathan, Nov 02 '17 at 03:23
I tried the code below but it's still telling me i can't use special character. `Dim myString As String Dim newString As String myString = "Login/Password" newString = Replace(Replace(myString, "/", " "), "!", " ") Sheets("Sheet5").Name = Range("L2") End Sub` — Jonathan, Nov 02 '17 at 03:36

paul bica · Accepted Answer · 2017-11-28T05:25:06.360

Both functions bellow will return a clean sheet name; size of 31 chars or less (max sheet name len)

Option Explicit

Public Function CleanWsName(ByVal wsName As String) As String
    Const x = vbNullString

    wsName = Trim$(wsName)    'Trim, then remove [ ] / \ < > : * ? | "
    wsName = Replace(Replace(Replace(wsName, "[", x), "]", x), " ", x)
    wsName = Replace(Replace(Replace(wsName, "/", x), "\", x), ":", x)
    wsName = Replace(Replace(Replace(wsName, "<", x), ">", x), "*", x)
    wsName = Replace(Replace(Replace(wsName, "?", x), "|", x), Chr(34), x)

    If Len(wsName) = 0 Then wsName = "DT " & Format(Now, "yyyy-mm-dd hh.mm.ss")
    CleanWsName = Left$(wsName, 31)         'Resize to max len of 31
End Function

Public Function CleanWsName2(ByVal wsName As String) As String
    Dim specialChars As Variant, i As Long

    specialChars = Split("[ ] / \ < > : * ? | " & Chr(34))

    wsName = Trim$(wsName)    'Trim, then remove [ ] / \ < > : * ? | "
    For i = 0 To UBound(specialChars)
        wsName = Replace(wsName, specialChars(i), vbNullString)
    Next
    wsName = Replace(wsName, " ", vbNullString)

    If Len(wsName) = 0 Then wsName = "DT " & Format(Now, "yyyy-mm-dd hh.mm.ss")
    CleanWsName2 = Left$(wsName, 31)     'Resize to max len of 31
End Function

To call it from your code use

Worksheets("Sheet3").Name = CleanWsName(Worksheets("Sheet3").Range("L2").Value2)

or to test it

wsName = CleanWsName2(" [ ] / \ < > : * ? | ""  ")

Edit

If you need to not rename based on a condition (L2 blank) call the function only if L2 is not empty:

Public Sub TestWSRename()
    Dim ws As Worksheet

    For Each ws in Thisworkbook.Worksheets
        With ws
            If Len(.Range("L2").Value2) > 0 Then .Name = CleanWsName(.Range("L2").Value2)
        End With
    Next
End Sub

GREAT! it works! im studying your code now for future use thanks again! — Jonathan, Nov 02 '17 at 03:44
I’m glad it helped. If you manually paste some invalid character like [ ] / \ ? you’ll get a message showing you the requirements for sheet names. 1. `Trim()` removes leading and trailing spaces from the string, 2. `Replace(wsName, "/", vbNullString)` replaces all instances of “/“ from the string with Nothing (an empty string). 3. `Left$(wsName, 31)` extracts the first 31 characters from the begining of the string (drops the extra chars from the end if it’s too long — paul bica, Nov 02 '17 at 03:55
Hi Paul, What if the value in L2 is blank? It's put in to blank purposely, how can we not rename it if the value is blank and stays at "Sheet#" name — Jonathan, Nov 07 '17 at 05:35
@Jonathan - I edited the answer to show how you can rename the sheet based on a condition (check if L2 is blank before calling the function) — paul bica, Nov 08 '17 at 01:06
with the `With Worksheets("Sheet2")` code, how about if i have multiple sheet and those sheets changes everyday? — Jonathan, Nov 28 '17 at 05:18
Made another edit to show how you can iterate through all sheets in the file. If you need to exclude some sheet use conditions like `If ws.Name <> “Sheet1* Then` — paul bica, Nov 28 '17 at 05:28

Renaming an Excel sheet from a cell that has special character

1 Answers1