Check If Multiple Sheets Exist

[WorkMC]

I have two workbooks open, the first is a summary, the second has new data that needs transferring into the summary. Before transferring the data I need to check that 10 specific sheets exist in the file with the new data.

I have found some code that checks for one sheet name, but really need to check for 10 specific names, if any are missing then I need a message to appear, listing the missing ones and stopping the macro from proceeding, cany anyone help?


Dim mySheetName As String, mySheetNameTest As String

ActiveWindow.ActivateNext
mySheetName = "Data"
On Error Resume Next
mySheetNameTest = Worksheets(mySheetName).Name
If Err.Number = 0 Then
MsgBox "The sheet named ''" & mySheetName & "'' DOES exist in this workbook."
Else
Err.Clear
MsgBox "The sheet named ''" & mySheetName & "'' does NOT exist in this workbook."
End If

 

[davez]

Maybe a way would be to add a sheet to the data workbook & list all worksheet names which the code below will do, then list the names needed & match to check if any are missing.

Sub SheetNames()
    
    Sheets.Add.Name = ("ws names")
    
    For i = 1 To Sheets.Count
        Cells(i, 1) = Sheets(i).Name
    Next i
End Sub