可通过vba宏一键批量导出excel多工作表为独立文件：一、基础拆分保留原结构；二、增强版保留格式与公式；三、支持自定义路径与命名规则；四、可跳过隐藏表及黑名单表名。
如果您在Excel中拥有多个工作表，需要将每个工作表保存为独立的Excel文件，则可通过VBA宏实现一键批量导出。以下是实现该功能的具体步骤：
一、使用基础VBA代码拆分工作表
该方法通过遍历当前工作簿中的所有工作表，逐一新建工作簿、复制内容并另存为独立文件，适用于常规格式（.xlsx）导出，不修改原始文件结构。
1、按 Alt + F11 打开VBA编辑器。
2、在左侧工程资源管理器中，右键点击当前工作簿名称，选择“插入” → “模块”。
3、在新模块窗口中粘贴以下代码：
Sub SplitSheetsToFiles()
Dim ws As Worksheet
Dim newWb As Workbook
Dim savePath As String
savePath = ThisWorkbook.Path & "\"
For Each ws In ThisWorkbook.Worksheets
Set newWb = Workbooks.Add
ws.Copy Before:=newWb.Sheets(1)
Application.DisplayAlerts = False
newWb.SaveAs Filename:=savePath & ws.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
newWb.Close SaveChanges:=False
Application.DisplayAlerts = True
Next ws
End Sub
4、关闭VBA编辑器，返回Excel界面，按 Alt + F8 调出宏对话框，选择“SplitSheetsToFiles”，点击“运行”。
二、保留原格式与公式引用的拆分方案
该方案确保每个导出文件完整保留源工作表的单元格格式、条件格式、数据验证及公式（不含跨表引用），避免因链接断裂导致显示错误。
1、在VBA编辑器中新建模块，粘贴以下代码：
Sub SplitSheetsPreserveFormatting()
Dim ws As Worksheet
Dim newWb As Workbook
Dim savePath As String
savePath = ThisWorkbook.Path & "\"
For Each ws In ThisWorkbook.Worksheets
Set newWb = Workbooks.Add(xlWBATWorksheet)
ws.Cells.Copy
newWb.Sheets(1).Cells.PasteSpecial Paste:=xlPasteAll
newWb.Sheets(1).Name = ws.Name
Application.CutCopyMode = False
Application.DisplayAlerts = False
newWb.SaveAs Filename:=savePath & ws.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
newWb.Close SaveChanges:=False
Application.DisplayAlerts = True
Next ws
End Sub
2、保存代码后，在Excel中执行该宏。
三、按指定文件夹路径与命名规则导出
该方法支持自定义保存路径及文件名规则（如添加日期前缀、过滤非法字符），避免覆盖已有文件，并自动创建目标文件夹。
1、在VBA编辑器中插入新模块，输入以下代码：
Sub SplitWithCustomPathAndName()
Dim ws As Worksheet
Dim newWb As Workbook
Dim savePath As String, fileName As String
savePath = "C:\Excel_Split_Output\"
MkDir savePath
For Each ws In ThisWorkbook.Worksheets
fileName = Replace(ws.Name, ":", "_")
fileName = Replace(fileName, "\", "_")
fileName = Replace(fileName, "/", "_")
fileName = Replace(fileName, "?", "_")
fileName = Replace(fileName, "*", "_")
fileName = Replace(fileName, "[", "_")
fileName = Replace(fileName, "]", "_")
Set newWb = Workbooks.Add(xlWBATWorksheet)
ws.Cells.Copy
newWb.Sheets(1).Cells.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
Application.DisplayAlerts = False
newWb.SaveAs Filename:=savePath & fileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
newWb.Close SaveChanges:=False
Application.DisplayAlerts = True
Next ws
End Sub
2、修改代码中 savePath = "C:\Excel_Split_Output\" 为目标路径，确保路径末尾含反斜杠。
四、跳过隐藏工作表与特定名称工作表
该方案可排除隐藏工作表，同时支持设置黑名单（如跳过名为“汇总”“目录”“说明”的工作表），防止误导出非数据类表页。
1、在VBA编辑器中新建模块，粘贴如下代码：
Sub SplitSkipHiddenAndBlacklist()
Dim ws As Worksheet
Dim newWb As Workbook
Dim savePath As String, skipList As Variant
savePath = ThisWorkbook.Path & "\"
skipList = Array("汇总", "目录", "说明", "Index")
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
If Not IsInArray(ws.Name, skipList) Then
Set newWb = Workbooks.Add(xlWBATWorksheet)
ws.Cells.Copy
newWb.Sheets(1).Cells.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
Application.DisplayAlerts = False
newWb.SaveAs Filename:=savePath & ws.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
newWb.Close SaveChanges:=False
Application.DisplayAlerts = True
End If
End If
Next ws
End Sub
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
Dim element As Variant
For Each element In arr
If element = stringToBeFound Then
IsInArray = True
Exit Function
End If
Next element
IsInArray = False
End Function
2、在 skipList = Array(...) 中增删需跳过的表名。
