VBA Confirm Sheet Existence-CodePudding

I'm trying to verify that a sheet exists in my workbook. It will confirm if the sheet name in the workbook exists in my array. If it does not exist then a new worksheet will be added and renamed based on the array. If it does exist, I want the code to continue with checking the next worksheet name.

This is what I have so far but my last array value "Test 7" won't pop up in my new worksheets added. It will only show "Test7" as the new name. Please help!

Dim SheetNames() As Variant
SheetNames()= Array("Test1","Test2","Test3","Test4","Test5","Test6","Test7")
For n =LBound(SheetNames) To UBound(SheetNames)
On Error Resume Next
   If Not Worksheets(SheetNames(n)).Name=SheetNames(n) Then
        Set cws = wb.Worksheets.Add(After:=ws)
   End If
   Next

CodePudding user response：

You should cancel the On Error Resume Next as soon as you no longer need it, or you may be hiding unexpected problems in the rest of your code.

Sub tester()
    Dim SheetNames() As Variant, ws As Worksheet, wb As Workbook, n As Long
    
    SheetNames() = Array("Test1", "Test2", "Test3", _
                   "Test4", "Test5", "Test6", "Test7")
    
    Set wb = ThisWorkbook 'for example
    
    For n = LBound(SheetNames) To UBound(SheetNames)
        Set ws = Nothing                       'reset ws to Nothing
        On Error Resume Next                   'ignore errors
        Set ws = wb.Worksheets(SheetNames(n))  'try to set `ws`
        On Error GoTo 0                        'stop ignoring errors
        If ws Is Nothing Then                  'got a sheet?
            Set ws = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
            ws.Name = SheetNames(n)
        End If
   Next
End Sub

CodePudding user response：

Add Missing Worksheets

Option Explicit

Sub AddMissingWorksheets()
    
    Dim SheetNames(): SheetNames = Array( _
        "Test1", "Test2", "Test3", "Test4", "Test5", "Test6", "Test7")
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sh As Object, SheetName
    
    For Each SheetName In SheetNames
        On Error Resume Next
            Set sh = wb.Sheets(SheetName)
        On Error GoTo 0
        If sh Is Nothing Then ' sheet doesn't exist
            wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = SheetName
        Else ' sheet exists
            Set sh = Nothing ' reset for the next iteration
        End If
    Next SheetName

    MsgBox "Missing worksheets added.", vbInformation

End Sub