Excel 365 VBA遇「无效工作表/图表名称」错误，仅部分设备崩溃

核心问题分析 #

报错「Invalid name for a sheet or chart」本质是代码尝试访问的工作表名称Sheet1在同事的Excel环境中不存在，可能的原因：

• 同事的Excel语言/区域设置与你不同，新建工作簿的默认工作表名不是Sheet1（比如部分非英文版本默认是本地化名称，如法语Feuille1、德语Tabelle1）
• 同事的Excel默认模板被修改，新建工作簿的初始工作表名已变更

修复步骤 #

1. 用索引或变量替代硬编码的工作表名称 #

新建工作簿时直接将工作表赋值给变量，避免依赖固定名称：

' 替换原新建工作簿的代码段
Dim ws As Worksheet
Set wb = Workbooks.Add
Set ws = wb.Sheets(1) ' 直接引用第一个工作表，无需依赖名称

后续所有涉及wb.Sheets("Sheet1")的地方，全部替换为ws，比如：

' 替换数据复制的目标位置
Workbooks("THE_FILE_FROM_SERVER").Sheets("Sheet1").Cells.Copy Destination:=ws.Range("A1")

' 替换冻结首行的代码（同时去掉Activate操作）
With wb.Windows(1)
    .SplitColumn = 0
    .SplitRow = 1
    .FreezePanes = True
End With

' 替换重命名代码（注意：/是文件名非法字符，改为-避免后续保存报错）
ws.Name = "Open Orders Status" & " " & Format(Date, "yyyy-mm-dd")

2. 移除不必要的Activate和Select操作 #

原代码大量使用Activate和Select，不仅容易引发上下文错误，还降低运行效率。直接通过变量操作对象：

' 替换列清理代码，无需Select
ws.Columns("A:C").Delete Shift:=xlToLeft
ws.Columns("C:E").Delete Shift:=xlToLeft
ws.Columns("D:F").Delete Shift:=xlToLeft
ws.Columns("K:K").Delete Shift:=xlToLeft
ws.Columns("L:Y").Delete Shift:=xlToLeft
ws.Columns("M:R").Delete Shift:=xlToLeft
ws.Columns("N:AT").Delete Shift:=xlToLeft

' 替换自动筛选代码
ws.Rows("1:1").AutoFilter

3. 修正文件名非法字符 #

原重命名代码中使用/作为日期分隔符，/是Windows文件名的非法字符，会导致后续保存工作簿失败，改为-即可解决。

完整优化后的关键代码片段 #

'Optimalization activated
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False

On Error GoTo ErrorHandler

Dim myValue As String
Dim wb As Workbook
Dim ws As Worksheet ' 新增工作表变量

'Welcome Prompt/clipboard thingy
MS = "Hello! Before we start, please prepare Customer ID. We'll need it later" & vbNewLine & vbNewLine
MS = MS & "CLICK ON THE 'OK' BUTTON ONCE COPIED"
MsgBox MS, vbOKOnly, "Welcome - let's prepare Customer ID"

'New excel
Set wb = Workbooks.Add
Set ws = wb.Sheets(1) ' 直接绑定第一个工作表

'Data from server - copy & close
Dim sourceWb As Workbook
Set sourceWb = Workbooks.Open("\\THE_FILE_FROM_SERVER.xlsx")
sourceWb.Sheets("Sheet1").Cells.Copy Destination:=ws.Range("A1")
sourceWb.Close SaveChanges:=False

'Column cleanup
ws.Columns("A:C").Delete Shift:=xlToLeft
ws.Columns("C:E").Delete Shift:=xlToLeft
ws.Columns("D:F").Delete Shift:=xlToLeft
ws.Columns("K:K").Delete Shift:=xlToLeft
ws.Columns("L:Y").Delete Shift:=xlToLeft
ws.Columns("M:R").Delete Shift:=xlToLeft
ws.Columns("N:AT").Delete Shift:=xlToLeft

'Borders added
With ws.Columns("A:M").Borders
    .xlDiagonalDown.LineStyle = xlNone
    .xlDiagonalUp.LineStyle = xlNone
    With .xlEdgeLeft
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With .xlEdgeTop
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With .xlEdgeBottom
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With .xlEdgeRight
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With .xlInsideVertical
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With .xlInsideHorizontal
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End With

ws.Rows("1:1").AutoFilter

'Top row freeze
With wb.Windows(1)
    .SplitColumn = 0
    .SplitRow = 1
    .FreezePanes = True
End With

'Renaming
ws.Name = "Open Orders Status" & " " & Format(Date, "yyyy-mm-dd")

ErrorHandler:
' 恢复Excel设置
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
If Err.Number <> 0 Then
    MsgBox "Error: " & Err.Description, vbCritical
End If

内容的提问来源于stack exchange，提问作者Totti