excel - 运行时错误 1004 对象“工作表”的方法“添加”失败 - 添加工作表

Question

我已经搜索了这个问题，并且从其他线程中它似乎与受保护的工作表、隐藏的工作表或不正确的文件名有关，但对我来说似乎都不是问题。

我只有一个电子表格文件有这个问题。此工作簿只有一张，我正在尝试添加一张名为“Sheet4”的工作表。

我已经使用多个其他电子表格进行了测试，并且效果很好。

这是代码：

Sub TestAddingSheet()


Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim WbName As String

WbName = ActiveWorkbook.Name

Set wb1 = Workbooks(WbName)
Set wb2 = Workbooks.Open("C:\test\test.xlsx")

Set sh1 = wb1.Sheets.Add(After:=Sheets(Sheets.Count))
sh1.Name = "Sheet4"
Set sh2 = wb1.Worksheets(1)

End Sub

我可以让它工作的方法是在添加新工作表之前激活 wb1，如下所示：

Sub TestAddingSheet()


Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim WbName As String

WbName = ActiveWorkbook.Name

Set wb1 = Workbooks(WbName)
Set wb2 = Workbooks.Open("C:\test\test.xlsx")

wb1.Activate
Set sh1 = wb1.Sheets.Add(After:=Sheets(Sheets.Count))
sh1.Name = "Sheet4"
Set sh2 = wb1.Worksheets(1)

End Sub

为什么原始代码不适用于所有电子表格？

标签： excelvba