Excel VBA：引用新建Workbook时重复创建的问题求助

我想从现有工作簿的Sheet1复制数据，新建一个工作簿，把数据粘贴进去后保存关闭。但每次引用新建的工作簿时，Excel都会额外创建一个新文件，肯定是哪里犯了基础错误。

原代码：

Dim oldWB, newWB As Workbook

Set oldWB = ActiveWorkbook
Set newWB = Workbooks.Add

oldWB.Worksheets("Sheet1").Copy
newWB.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
newWB.SaveAs "W:\\filepath\\CopyPasteTest.xlsx"
newWB.Close

错误原因 #

问题出在oldWB.Worksheets("Sheet1").Copy这一行：当直接调用工作表的Copy方法且不指定目标位置时，Excel会自动新建一个包含该工作表的工作簿，这就导致你同时打开了两个新工作簿——一个是用Workbooks.Add创建的空工作簿，另一个是Copy方法自动生成的工作簿。

解决方法 #

方法一：利用Copy方法直接生成目标工作簿（更简洁） #

不需要提前创建空工作簿，直接通过Copy方法生成包含目标工作表的新工作簿，再对其进行操作：

Dim oldWB As Workbook
Dim newWB As Workbook

Set oldWB = ActiveWorkbook
' 复制Sheet1到新工作簿，此操作会自动创建新工作簿
oldWB.Worksheets("Sheet1").Copy
' 将自动生成的新工作簿赋值给变量
Set newWB = ActiveWorkbook

' 转换为值并保留数字格式（无需粘贴操作）
With newWB.Worksheets(1).UsedRange
    .Value = .Value ' 清除公式，保留值
    .NumberFormat = oldWB.Worksheets("Sheet1").UsedRange.NumberFormat ' 同步数字格式
End With

newWB.SaveAs "W:\filepath\CopyPasteTest.xlsx"
newWB.Close

方法二：先创建空工作簿，再定向复制数据 #

如果坚持先创建空工作簿，需明确指定复制的目标位置，避免自动生成额外工作簿：

Dim oldWB As Workbook
Dim newWB As Workbook
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet

Set oldWB = ActiveWorkbook
Set newWB = Workbooks.Add
Set sourceSheet = oldWB.Worksheets("Sheet1")
Set targetSheet = newWB.Worksheets("Sheet1")

' 定向复制值和数字格式
sourceSheet.UsedRange.Copy
targetSheet.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False ' 清除剪贴板状态

newWB.SaveAs "W:\filepath\CopyPasteTest.xlsx"
newWB.Close

额外注意事项 #

1. 变量声明：原代码中Dim oldWB, newWB As Workbook仅newWB为Workbook类型，oldWB是Variant类型，建议改为Dim oldWB As Workbook, newWB As Workbook，避免类型错误。
2. 文件路径：VBA中直接使用单反斜杠\即可，无需双反斜杠（双反斜杠是字符串转义场景的写法）。

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