从具有特殊字符的单元格重命名 Excel 工作表

Question

我已经创建了复制"L2"中值值的宏代码，该代码有效，但我的问题是如果"L2"的值具有特殊字符，则会给我一个错误。我知道重命名工作表时不允许使用特殊字符的规则。

有没有办法绕过它？ 假设它从 （L2） 复制除特殊字符以外的文本？谢谢。

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

下面是我从"透视"表启动表的代码，需要根据 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

Answer 1

下面的两个函数都将返回一个干净的工作表名称; 大小不超过 31 个字符（最大工作表名称 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

---

从代码调用它，请使用

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

或测试它

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

---

编辑

如果不需要根据条件（L2 空白）重命名，请仅在 L2 不为空时才调用该函数：

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