时间戳

首页 > 解决方案 > 如何将多个 Excel 工作表迁移到一个主工作簿

excel - 如何将多个 Excel 工作表迁移到一个主工作簿

问题描述

我通常有几个带有单独工作表的工作簿来迁移到主工作簿，想想：

回顾一下名为 2020Recap.xlxm 的单张纸。我想构建一个宏来收集每个工作表并写入回顾工作簿，我从下面的 VBA 脚本开始。

我在使用下面的 VBA 时遇到问题

我还想添加到宏中以使用文件名作为工作表名称。

Sub CopySheets()

Workbooks("C:\Test\Account AR Aging Patient.xlsx").Sheets("Account AR Aging Patient.xlsx").Copy _
  After:=Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets(Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets.Count)

Workbooks("C:\Test\Account AR Aging Payer.xlsx").Sheets("Account AR Aging Payer.xlsx").Copy _
    After:=Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets(Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets.Count)

Workbooks("C:\Test\AR History.xlsx").Sheets("AR History").Copy _
    After:=Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets(Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets.Count)

End Sub

标签： excelvba

解决方案

您在工作表名称中有工作簿名称。尝试：

Dim wb_source As Workbook
Dim wb_target As Workbook

Application.DisplayAlerts = False

Set wb_source = Workbooks.Open("C:\Test\Account AR Aging Patient.xlsx")
Set wb_target = Workbooks.Open("c:\Test\MEBIllingOffice.xlsm")

'assuming the sheet name is "Sheet1"
wb_source.Sheets("Sheet1").Copy after:=wb_target.Sheets(wb_target.Sheets.Count)
wb_source.Close

'next source.  note: sheet names must be unique within target
Set wb_source = Workbooks.Open("C:\Test\Account AR Aging Payer.xlsx")
wb_source.Sheets("Sheet2").Copy after:=wb_target.Sheets(wb_target.Sheets.Count)
wb_source.Close

wb_target.Save
wb_target.Close

Application.DisplayAlerts = True

推荐阅读