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

Trying to delete all sheets in a workbook, then add other sheets in with VBA

提问by aCarella

I keep running into errors trying to delete most sheets in a workbook, then add them back in.

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

I cannot delete all sheets in a workbook; at least one sheet needs to be there. Fine. So I first create a sheet called TempSheet.

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

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

So now, I want to delete all of the sheets besides TempSheetin my workbook. So I'm trying to do this:

所以现在，我想删除除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

When I step through my code and get to Line X, I get the error Can't enter break mode at this time. I've tried replacing all the code on Line Xwith just ws1.Delete, I get the same message. I would like to at least step through the code so I can see the sheets being deleted.

当我逐步执行我的代码并到达时Line X，我收到错误消息Can't enter break mode at this time。我试过用Line Xjust替换所有代码ws1.Delete，我得到相同的消息。我想至少单步执行代码，以便我可以看到正在删除的工作表。

The other part of my code is such that I would like to take all of the sheets from another workbook and place them in the first workbook where I deleted all of the sheets from above. My code is below:

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

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

I put a breakpoint on Line Y, and after I get the Can't enter break mode at this timeerror, I hit the Continuebutton. Instead of stopping at the break point, I get the error Run-time error '424'. Object required, which doesn't show me which line it is referring to and doesn't let me debug (the button is grey'd out and the only button I can select is end).

我在 上设置了一个断点Line Y，在Can't enter break mode at this time出现错误后，我按下了Continue按钮。我没有在断点处停止，而是收到错误Run-time error '424'. Object required，它没有显示它指的是哪一行，也不允许我进行调试（按钮呈灰色显示，我可以选择的唯一按钮是end）。

Any suggestions on how I can achieve this?

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

回答by David Zemens

Clean up your syntax, there's no need to be redundant like for example:

清理你的语法，没有必要像例如：

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

This is basically saying: ActiveWorkbook.Activate, which not only doesn't do anything(literally), it's also almost never necessary to rely on Activateor Selectfor anything, assuming you use appropriately scoped object variables.

这基本上是说: 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

This shouldwork. If you can't enter break mode, then there is something else going on with your PC.

这应该有效。如果您无法进入中断模式，则您的 PC 会发生其他问题。

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

当我单步执行代码并到达 X 行时，出现错误此时无法进入中断模式。

If you're stepping through the code, you're alreadyin break mode, so this doesn't really make sense. See if any of these suggestions help identify the source of the error(s):

如果您正在逐步执行代码，那么您已经处于中断模式，因此这实际上没有意义。查看以下任何建议是否有助于确定错误来源：

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 DoEventsstatement after the Sheets.Add
4. Use a MsgBoxinstead 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.Addinstead; practically speaking, there's no reason to put the breakpoint on a Printstatement 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.

1. 从 VBE 调试菜单，“编译 VBA 项目”，值得一试。
2. 完全删除该行。运行代码。然后将行放回并再次尝试使用断点。
3. DoEvents在后面添加一条语句Sheets.Add
4. MsgBox在 a 上使用 a而不是断点Debug.Print。与消息框中显示，尝试使用手动打破ctrl+ fn+ End。（在这一点上，“打破”是没有必要的，但看看你是否可以这样打破会很有趣）
5. Sheets.Add改为设置断点；实际上，Print如果您可以将断点放在前一行，则没有理由将断点放在语句上。
6. 有没有插件？如果是这样，请禁用所有这些并一次重新启用一个，测试以查看哪一个可能导致错误。

回答by Shai Rado

You already set the ws1sheet object, and looping inside the workbook, you can directly delete the ws1object, without over complicating the syntax.

您已经设置了工作ws1表对象，并在工作簿内部循环，您可以直接删除该ws1对象，而无需过度复杂的语法。

Dim ws1 As Worksheet

For Each ws1 In Application.Workbooks(targetWorkbook).Worksheets
    If ws1.Name <> "TempSheet" Then
        ws1.Delete
    End If
Next

You could just set the Workbook also like :

您也可以像这样设置工作簿：

Dim wb1 As Workbook

Set wb1 = ActiveWorkbook ' better yet use ThisWorkbook if the workbook is the one with this code in it

For Each ws1 In wb1.Worksheets
    If ws1.Name <> "TempSheet" Then
        ws1.Delete
    End If
Next

Full code:

完整代码：

Sub Add_DeleteWorksheets()

Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim tmpsht As Worksheet

Set wb1 = ActiveWorkbook ' better yet use ThisWorkbook if the workbook is the one with this code in it
Set tmpsht = wb1.Worksheets.Add
tmpsht.Name = "TempSheet"

For Each ws1 In wb1.Worksheets
    If ws1.Name <> tmpsht.Name Then
        ws1.Delete
    End If
Next

End Sub

回答by user3598756

Other that what I proposed in my comment, you can try these following two approaches

除了我在评论中提出的内容之外，您可以尝试以下两种方法

---

---

Approach 1- first insert new workbooks and then delete existing ones

方法 1- 首先插入新工作簿，然后删除现有工作簿

this approach has the advantage you don't need any "temp" sheet, and the possible drawback of hitting memory limit in the temporary stage your active workbook has both "old" and "new" worksheets

这种方法的优点是您不需要任何“临时”工作表，并且在临时阶段达到内存限制的可能缺点是您的活动工作簿同时具有“旧”和“新”工作表

Option Explicit

Sub delsheets1()
    ReDim existingSheets(1 To Worksheets.Count) As String
    Dim isht As Long

    For isht = 1 To Worksheets.Count
         existingSheets(isht) = Worksheets(isht).Name
    Next

    Application.DisplayAlerts = False
    Workbooks("Cartel2").Worksheets.Copy After:=Worksheets(1)
    Worksheets("OtherWorkbook").Delete
    Application.DisplayAlerts = True
End Sub

---

---

Approach 2- first delete existing ones and then insert new workbooks

方法 2- 首先删除现有工作簿，然后插入新工作簿

this is the same approach as yours and has the (logic only, indeed) drawback of inserting a new "temp" sheet

这与您的方法相同，并且具有插入新“临时”表的（实际上仅逻辑）缺点

may be the "group" deleting technique avoids the issues you're having with your current code

可能是“组”删除技术避免了您在当前代码中遇到的问题

Option Explicit

Sub delsheets2()
    ReDim existingSheets(1 To Worksheets.Count) As String
    Dim isht As Long

    For isht = 1 To Worksheets.Count
         existingSheets(isht) = Worksheets(isht).Name
    Next

    Worksheets.Add.Name = "TempSheet"

    Application.DisplayAlerts = False
    Worksheets(existingSheets).Delete
    Workbooks("OtherWorkbook").Worksheets.Copy After:=Worksheets(1)
    Worksheets("TempSheet").Delete
    Application.DisplayAlerts = True
End Sub

回答by agcala

Here is yet another one. This routine deletes all sheets except one when you click on the newsheet button:

这是另一个。当您单击 newsheet 按钮时，此例程将删除除一张以外的所有工作表：

Public Sub Workbook_NewSheet(ByVal Sh As Object)

  Application.EnableEvents = False
  Application.DisplayAlerts = False
  For i = Workbooks(1).Sheets.Count - 1 To 1 Step -1
    Workbooks(1).Sheets(i).Delete
  Next
  Sheets(1).Name = "Sheet1"
  Application.DisplayAlerts = True
  Application.EnableEvents = True
End Sub