技术问询：将Excel多工作表拆分至独立文件，排查VBA代码异常

问题根源分析 #

你的代码出现重复文件，主要有以下几个潜在原因：

• 未显式声明Sheet变量，可能和Excel内置对象产生命名冲突，导致循环中引用的工作表对象异常。
• 依赖ActiveWorkbook判断当前工作簿，复制工作表后激活的工作簿可能出现识别偏差，导致每次覆盖同一文件。
• 工作表名称包含Excel文件名非法字符（如\ / : * ? " < > |），保存时系统自动替换这些字符，最终生成同名文件。

修正后的代码 #

Option Explicit

Sub Split_Sheet_into_ExcelFiles()
    Dim FilePath As String
    Dim ws As Worksheet
    Dim newWB As Workbook
    
    FilePath = ThisWorkbook.Path
    If FilePath = "" Then
        MsgBox "请先保存当前工作簿，再执行拆分操作！", vbExclamation
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each ws In ThisWorkbook.Sheets
        ws.Copy
        Set newWB = Application.ActiveWorkbook
        ' 清理工作表名称中的非法字符
        Dim cleanName As String
        cleanName = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(ws.Name, "\", ""), "/", ""), ":", ""), "*", ""), "?", ""), """", ""), "<", ""), ">", "")
        cleanName = Replace(cleanName, "|", "")
        
        newWB.SaveAs Filename:=FilePath & "\" & cleanName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        newWB.Close SaveChanges:=False
    Next ws
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "拆分完成！", vbInformation
End Sub

关键改动说明 #

1. 添加Option Explicit强制变量声明，避免命名冲突。
2. 用ws代替Sheet作为工作表变量名，规避内置对象名冲突。
3. 将复制后的新工作簿赋值给newWB变量，不再依赖不稳定的ActiveWorkbook。
4. 增加工作表名称非法字符清理逻辑，防止因命名问题导致文件覆盖。
5. 增加判断：若当前工作簿未保存（路径为空），弹出提示并终止操作，避免保存路径错误。

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