VBA With语句创建工作表：如何仅新增ClientList和ProviderList且不重复

Hey there! The issue you're facing is super common—your current code tries to create those two worksheets every time you run it, and Excel throws an error because you can't have two sheets with the same name in a workbook.

The solution is simple: check if the worksheet exists before trying to create it. Here's how to adjust your code to do that cleanly:

First, add a reusable helper function to check for existing sheets. This will make your main code cleaner and let you reuse the check elsewhere if needed:

Function SheetExists(sheetName As String, Optional targetWorkbook As Workbook) As Boolean
    ' If no workbook is specified, default to the current workbook
    If targetWorkbook Is Nothing Then Set targetWorkbook = ThisWorkbook
    
    Dim sheetCheck As Worksheet
    ' Use error handling to check if the sheet exists
    On Error Resume Next
    Set sheetCheck = targetWorkbook.Sheets(sheetName)
    On Error GoTo 0
    
    ' Return True if the sheet was found, False otherwise
    SheetExists = Not sheetCheck Is Nothing
End Function

Then modify your main CreateSheet subroutine to use this check before creating each sheet:

Sub CreateSheet()
    With ThisWorkbook
        ' Only create ClientList if it doesn't already exist
        If Not SheetExists("ClientList") Then
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "ClientList"
        End If
        
        ' Only create ProviderList if it doesn't already exist
        If Not SheetExists("ProviderList") Then
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "ProviderList"
        End If
    End With
End Sub

How this works: #

• The SheetExists function uses error handling to attempt to reference the sheet by name. If the sheet doesn't exist, the reference will be Nothing, and the function returns False.
• In your main sub, we only run the Sheets.Add line if the function returns False (meaning the sheet doesn't exist yet).

Now you can run this code as many times as you want—no more duplicate sheet errors!

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