excel - 尝试删除工作簿中的所有工作表，然后使用 VBA 添加其他工作表

Question

我一直在尝试删除工作簿中的大多数工作表，然后将它们重新添加时遇到错误。

我无法删除工作簿中的所有工作表；至少需要一张纸。美好的。所以我首先创建了一个名为 TempSheet 的工作表。 .

targetWorkbook = ActiveWorkbook.Name
'/* ...more code... */
Application.Workbooks(targetWorkbook).Activate
Application.Workbooks(targetWorkbook).Worksheets.Add.Name = "TempSheet"

所以现在，我想删除除了 TempSheet 之外的所有工作表。在我的工作簿中。所以我正在尝试这样做:

For Each ws1 In Application.Workbooks(targetWorkbook).Worksheets
    If ws1.Name <> "TempSheet" Then
        Workbooks(targetWorkbook).Sheets(ws1.Name).Delete '/* Line X. */
    End If
Next

当我单步执行我的代码并到达 Line X 时，我收到错误 Can't enter break mode at this time .我已尝试替换 Line X 上的所有代码只需 ws1.Delete ，我得到同样的信息。我想至少逐步完成代码，以便我可以看到被删除的工作表。

我的代码的另一部分是，我想从另一个工作簿中取出所有工作表并将它们放在第一个工作簿中，我从上面删除了所有工作表。我的代码如下:

Application.Workbooks("OtherWorkbook").Activate '/* Line Y. */
For Each ws2 In Application.Workbooks(sheetsWorkbook).Worksheets
    ws2.Copy After:=Workbooks(targetWorkbook).Sheets(ws1.Name)
Next

我在 Line Y 上设置了断点, 在我得到 Can't enter break mode at this time 之后错误，我点击了 Continue按钮。我没有在断点处停止，而是收到错误 Run-time error '424'. Object required ，它没有告诉我它指的是哪一行，也不允许我调试(按钮是灰色的，我可以选择的唯一按钮是 end )。

关于如何实现这一目标的任何建议？

Answer 1

清理你的语法，不需要多余的，例如:

targetWorkbook = ActiveWorkbook.Name
Application.Workbooks(targetWorkbook).Activate

这基本上是在说: ActiveWorkbook.Activate ，它不仅不做任何事情(字面意思)，而且几乎不需要依赖 Activate或 Select对于任何事情，假设您使用适当范围的对象变量。

Option Explicit
Sub foo()
Dim tempSheet as Worksheet, ws as Worksheet, wb As Workbook
Set wb = ActiveWorkbook ' Or ThisWorkbook, or Workbooks("filename.xlsx"), etc.
Set tempSheet = wb.Sheets.Add

For Each ws in wb.Worksheets
    Application.DisplayAlerts = False
    If ws.Name <> tempSheet.Name Then
        ws.Delete
    End If
    Application.DisplayAlerts = True
Next

'Now copy the other worksheets:
Workbooks("OtherWorkbook").Worksheets.Copy After:=wb.Worksheets(1)

'Finally, remove tempsheet
tempSheet.Delete

End Sub

这应该有效。如果您无法进入中断模式，那么您的 PC 上还有其他问题。

When I step through my code and get to Line X, I get the error Can't enter break mode at this time.

如果您正在单步执行代码，那么您已经处于中断模式，因此这没有任何意义。看看这些建议是否有助于确定错误的来源:

Can't enter break mode at this time

1. From the VBE Debug menu, "Compile VBA Project", it's worth a shot.
2. Delete the line entirely. Run the code. Then put the line back in and try again with the breakpoint.
3. Add a DoEvents statement after the Sheets.Add
4. Use a MsgBox instead of a breakpoint on a Debug.Print. With the message box displayed, attempt to manually break using ctrl+fn+End. (At this point, "breaking" isn't necessary but it would be interesting to see whether you can break this way)
5. Put a breakpoint on Sheets.Add instead; practically speaking, there's no reason to put the breakpoint on a Print statement if you can just put it on the preceding line.
6. Are there any Addins? If so, disable all of them and re-enable one at a time, testing to see which one may contribute to the error.