VBA. sheet names (values) in named range. How to check if any of them don't exist?

Hi all

Currently I have a workbook where there is a sheet called properties import settings. I use this sheet to to keep some of my settings that I can edit on the fly if needed. Quite few of my macros fetch certain settings from this sheet (e.g. settings such as: sheet names, workbook locations, folders where to save stuff, etc).

Now below there is a fragment of my code that essentially picks up some sheet names from named range called name_of_the_sheet_SLOT3 (This named range is a dynamic named range so if I will add some more names to the list it will then automatically expand the named range. Currently it equates "X4:X15", but it will be be growing increasingly as the time goes on). So this code below picks up the sheet names and starts to transfer over information from opened workbook (this opened workbook is defied as wbOpened within code) to back to the original workbook (defied as wbOrig within code). I have included some comments to the code so its bit easier to follow. I know its bit messy.

    Dim ws As Worksheet, sht As Worksheet
wbOrig.Activate ' activate/move back to original workbook

    Dim NameOfTheSheet As Range, Rng As Range
    Set ws = Worksheets("properties import settings")
'----VVV----------named range that contains all the sheet names.-------------------
    Set NameOfTheSheet = ws.Range("name_of_the_sheet_SLOT3")
'Essentially it refers to: Worksheets("properties import settings").Range("X4:X15")
'----AAA---------------------------------------------------------------------------



'<-------THIS IS A SECTION WHERE I WOULD LIKE TO ADD VERIFICATION PROCESS


'----VVV----------LOOPED SECTION. Transfer information over from opened workbook back to the original workbook.
'Essentially it loops through the sheetnames declared in named range "name_of_the_sheet_SLOT3"

wbOpened.Activate ' activate workbook that was earlier opened with macro

    For Each Rng In NameOfTheSheet
        For Each sht In Sheets
            If sht.Name = Rng.Value Then
                sht.Visible = xlSheetVisible
                sht.Activate
                Dim a, i As Long
                With ActiveSheet: a = .Range(.[A2], .Cells(Rows.Count, "a").End(xlUp).Offset(, 14)): End With
                    For i = 1 To UBound(a)
                     a(i, 8) = Format(a(i, 8), "0.0"):
                wbOrig.Activate ' activate/move back to original workbook
                Next: Sheets("imported_Lost_Time").Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).Resize(UBound(a), 14) = a
                wbOpened.Activate ' activate workbook that was earlier opened with macro
            End If
        Next sht
    Next Rng

'----AAA----------END of LOOPED SECTION------------------------------------------------

wbOrig.Activate ' activate/move back to original workbook
wbOpened.Close SaveChanges:=False ' close opened workbook

The code above does its job and works just fine with small exception. If there is a sheet missing that was declared in within the named range it will simply carry on to the next bit. This is fine by the way.

Now the bit where I would like to have some help is to set up a VERIFICATION PROCESS that would check if all the worksheets declared in the named range (name_of_the_sheet_SLOT3) exist within this opened workbook. This should be performed before even trying to transfer information between these workbooks. If it finds any sheets that are missing from the list it should prompt the user with an error message and provide the details of which sheets are missing and then perform "Exit Sub".


I googled and experimented with some codes that I found from the net but I wasn't able to successfully adjust them to my needs. I wont even post my miserable attempts to solve it. It's very embarrassing. I 'm still such a newb when it comes to EXCEL!!!!

I would normally post example sheet but its rather difficult to do it at the moment.
Any help is very much appreciated.
Cheers