vba 删除工作表（如果存在）并创建一个新工作表

Delete worksheet if it exists and create a new one

提问by BIGGEST

I want to look through my Excel worksheets and find a sheet with a certain name and delete that sheet if it is found. Afterwards I want to create a sheet after all existing sheets with that name. My code is as follows:

我想查看我的 Excel 工作表并找到具有特定名称的工作表，如果找到则删除该工作表。之后我想在所有具有该名称的现有工作表之后创建一个工作表。我的代码如下：

For Each ws In Worksheets
    If ws.Name = "asdf" Then
        Application.DisplayAlerts = False
        Sheets("asdf").Delete
        Application.DisplayAlerts = True
        End
    End If
Next

Sheets.Add(After:=Sheets(Sheets.count)).Name = "asdf"

However this doesn't do both of these actions in one run of the code. If the sheet already exists it will simply delete the sheet and not make a new one like I want it to. I need to run it again for it to create a new one.

但是，这不会在一次代码运行中执行这两个操作。如果工作表已经存在，它只会删除工作表，而不是像我想要的那样制作新的工作表。我需要再次运行它才能创建一个新的。

How do I fix my code to delete the old sheet if it exists and create a new one?

如何修复我的代码以删除旧工作表（如果存在）并创建一个新工作表？

回答by user3598756

Instead of looping through Worksheets, you can test the existence of an item in the collection by trying and getting it:

Worksheets您可以通过尝试并获取它来测试集合中项目的存在，而不是循环遍历：

Function GetWorksheet(shtName As String) As Worksheet
    On Error Resume Next
    Set GetWorksheet = Worksheets(shtName)
End Function

If Not GetWorksheet("asdf") Is Nothing Then
    Application.DisplayAlerts = False
    Worksheets("asdf").Delete
    Application.DisplayAlerts = True
End If
Worksheets.Add(After:=sheets(sheets.Count)).name = "asdf"

However, the most straightforward method would be trying to delete the sheet while wrapped in a On Error Resume Next- On Error GoTo 0"block":

然而，最直接的方法将试图同时裹在删除该工作表On Error Resume Next- On Error GoTo 0“块”：

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("asdf").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add(After:=sheets(sheets.Count)).name = "asdf"

回答by Maximilian Peters

Remove the Endstatement, your code terminates after finding and deleting the worksheet asdf.

删除End语句，您的代码在找到并删除 worksheet 后终止asdf。

For Each ws In Worksheets
    If ws.Name = "asdf" Then
        Application.DisplayAlerts = False
        Sheets("asdf").Delete
        Application.DisplayAlerts = True
    End If
Next

Sheets.Add(After:=Sheets(Sheets.count)).Name = "asdf"

回答by ashleedawg

I disagree that the "most straightforward"approach is to intentionally generate (and suppress) an error. Personally I'd opt for the loop-to-locate method, only attempting deletion if the object exists.

我不同意“最直接”的方法是故意生成（并抑制）错误。我个人会选择 loop-to-locate 方法，只有在对象存在时才尝试删除。

If you're going to be re-creating the worksheet anyway, this will do the job in almost every case:

如果您无论如何要重新创建工作表，这几乎在所有情况下都可以完成：

Sheets("asdf").Cells.Delete  'deletes all cells in the specified worksheet

---

---

Safely Delete a Worksheet

安全删除工作表

If the one-liner (above) doesn't work for you (maybe due to some stubborn background colors/images) then here's a sub that you can call to delete any specified worksheet:

如果单行（上面）对您不起作用（可能是由于某些顽固的背景颜色/图像），那么您可以调用以下子程序来删除任何指定的工作表：

Sub deleteSheet(wsName As String)
  Dim ws As Worksheet
  For Each ws In ThisWorkbook.Sheets 'loop to find sheet (if it exists)
    Application.DisplayAlerts = False 'hide confirmation from user
    If ws.Name = wsName Then ws.Delete 'found it! - delete it
    Application.DisplayAlerts = True 'show future confirmations
  Next ws
End Sub

Call it simply like:

简单地称之为：

deleteSheet "asdf"

...where asdfis the name of the worksheet (tab) to delete.

...asdf要删除的工作表（选项卡）的名称在哪里。

---

---

Delete & Replace a Worksheet

删除和替换工作表

Alternatively, calling the sub below will delete and replace the worksheet, giving it the same name and putting it in the same tab position as the original:

或者，调用下面的 sub 将删除并替换工作表，为其赋予相同的名称并将其放在与原始相同的选项卡位置：

Sub resetSheet(wsName As String)
  Dim ws As Worksheet, ws2 As Worksheet
  For Each ws In ThisWorkbook.Sheets        'loop to find sheet (if it exists)
    If ws.Name = wsName Then                'found it!
      Set ws2 = ThisWorkbook.Sheets.Add(ws) 'add new sheet located before old one
      Application.DisplayAlerts = False     'hide confirmation from user
      ws.Delete                             'delete sheet (new one takes its place)
      Application.DisplayAlerts = True      'show future confirmations
      ws2.Name = wsName                     'rename new sheet to old name
      Exit Sub                              'finished! (no need to continue looping)
    End If
  Next ws
End Sub

---

---

About ThisWorkbook:

关于ThisWorkbook：

I used ThisWorkbookin both subs, which is important when running VBA that modifies other workbooks, or while you have multiple workbooks open at the same time.

我ThisWorkbook在两个子程序中都使用过，这在运行修改其他工作簿的 VBA 或同时打开多个工作簿时很重要。

Using ThisWorkbookensures that your code is always referring to the workbook where the code is located that called it.

使用ThisWorkbook可确保您的代码始终引用调用它的代码所在的工作簿。

Without it, for example, if you have two similar files open, and you're looking at one while you're waiting for VBA to run in the other workbook, if the workbook that's "on top" has a worksheet with the name of the one VBA's trying to delete, it will delete the worksheet in the current (active) workbook instead of the intended one.

没有它，例如，如果您打开了两个类似的文件，并且在等待 VBA 在另一个工作簿中运行时正在查看一个文件，如果“顶部”的工作簿有一个名称为VBA 试图删除的一个，它将删除当前（活动）工作簿中的工作表，而不是预期的工作表。

_{(...and you can't Ctrl+Zto undo VBA!)}

_{（...你不能Ctrl+Z撤消 VBA！）}