如何使用VBA一次性将多个Excel工作表复制到新工作簿

当然可以用数组一次性完成这个操作，VBA完全支持Worksheets(数组).Copy的写法，效果和你手动在GUI中多选工作表复制的逻辑完全一致，能完美保留工作表间的内部链接，不会指向原工作簿。

核心方案 #

直接构建包含目标工作表名称或对象引用的数组，调用Copy方法即可。这种方式复制的工作表会自动调整内部跨表链接，全部指向新工作簿内的对应表，避免逐张复制导致的链接混乱问题。同时，新工作簿不会携带原文件的VBA工程代码（仅工作表级代码会被复制，若不需要可后续删除），未包含在数组里的辅助工作表也不会被复制。

代码示例 #

示例1：按工作表名称指定目标数组

Sub CopySheetsWithLinks()
    Dim targetWorksheets As Variant
    Dim newWorkbook As Workbook
    
    ' 定义要复制的工作表名称数组（按需求修改）
    targetWorksheets = Array("销售数据", "利润分析", "月度报表")
    
    ' 一次性复制指定工作表到新工作簿
    ThisWorkbook.Worksheets(targetWorksheets).Copy
    
    ' 获取新生成的工作簿对象
    Set newWorkbook = ActiveWorkbook
    
    ' 可选：保存新工作簿
    newWorkbook.SaveAs "C:\目标路径\新报表.xlsx"
    
    ' --------------------------
    ' 可选：处理原工作簿（移除辅助表和VBA代码）
    ' 1. 删除辅助工作表（示例：删除名为"辅助计算"的表）
    Application.DisplayAlerts = False ' 关闭删除提示
    ThisWorkbook.Worksheets("辅助计算").Delete
    Application.DisplayAlerts = True
    
    ' 2. 移除原工作簿的VBA代码（需启用信任中心的宏操作权限）
    ' 若要删除整个VBA工程，可通过VBIDE对象模型实现，示例：
    ' Dim vbProj As VBIDE.VBProject
    ' Set vbProj = ThisWorkbook.VBProject
    ' vbProj.VBComponents.Remove vbProj.VBComponents("模块1") ' 删除指定模块
    ' 注意：使用VBIDE需在VBA编辑器中勾选"Microsoft Visual Basic for Applications Extensibility"引用
    ' --------------------------
End Sub

示例2：按工作表对象引用指定数组

如果需要动态筛选目标工作表（比如排除名称包含"辅助"的表），可以用对象数组：

Sub CopyFilteredSheets()
    Dim ws As Worksheet
    Dim targetSheetList As Collection
    Dim targetWorksheets() As Worksheet
    Dim i As Integer
    
    Set targetSheetList = New Collection
    
    ' 遍历原工作簿，筛选目标工作表（示例：排除名称含"辅助"的表）
    For Each ws In ThisWorkbook.Worksheets
        If InStr(ws.Name, "辅助") = 0 Then
            targetSheetList.Add ws
        End If
    Next ws
    
    ' 将Collection转换为数组
    ReDim targetWorksheets(1 To targetSheetList.Count)
    For i = 1 To targetSheetList.Count
        Set targetWorksheets(i) = targetSheetList(i)
    Next i
    
    ' 一次性复制到新工作簿
    ThisWorkbook.Worksheets(targetWorksheets).Copy
    
    ' 后续保存/处理逻辑同上
End Sub

关键说明 #

• 链接保留机制：一次性复制多工作表时，Excel会将所有选中表的内部链接视为一个整体，复制后自动将链接指向新工作簿内的对应表；而逐张复制时，后复制的表会默认链接到原工作簿中已复制的前一张表，导致链接异常。
• VBA代码处理：新工作簿不会继承原工作簿的VBA工程（如标准模块、类模块），仅会复制工作表自身的事件代码（如Worksheet_Change），若不需要可在新工作簿中删除对应工作表的代码模块。
• 注意事项：数组中的工作表必须属于同一个源工作簿；若使用名称数组，需确保名称拼写准确（Excel不区分大小写，但VBA会严格匹配）。

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