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

Originally Posted by mohd9876

you can do something like this:

Dim ws As Worksheet
For Each Rng In NameOfTheSheet
    On Error Resume Next
    Set ws = Worksheets(Rng.Value)
    On Error GoTo 0
    If Err.Number <> 0 Then
        MsgBox "Missing Sheet"
        Exit Sub
    End If
Next Rng

Thanks mohd
I did have to remove On Error GoTo 0 but it is now working.
I also added line to try to identify which sheet is missing. I added this before error message as below:

Dim ThisSht As String
ThisSht = ws1.Name
MsgBox "Sheet "" " & ThisSht & " "" is missing"

So now it looks like this:

Dim ws1 As Worksheet
For Each Rng In NameOfTheSheet
    On Error Resume Next


Set ws1 = Worksheets(Rng.Value)

If Err.Number <> 0 Then
Dim ThisSht As String
ThisSht = ws1.Name
MsgBox "Sheet "" " & ThisSht & " "" is missing"
        
        Exit Sub
    End If
Next Rng

This message to identify which sheet is missing is almost working. Let me explain. Lets say I have the following values as below within that named range:
M60
M61
M62
M66
M80
M85


Now in my other workbook I removed sheet M62 and I ran the code. It prompted me with error saying that Sheet "M61" is missing.
I repeated the same process but this time I removed M80 and this time it prompted with error saying that Sheet "M66" is missing.
So essentially it remembers ws1 = as the previous sheet name before it errors out. I did couple examples and its always the same. So I was wondering how to achieve the following:

Could I somehow tie ws1 or string value ThisSht and ask VBA to look through the named range, match this value, and pick the next one in line as a match and Message me the sheet that is missing?
So lets say if M80 is missing it will error out, and essentially ws1 would equal with M66. So now the code would look the list through and pick the next value after M66 which would be M80 in this case, and this happens to be the sheet that is missing.

It would be awesome feat to have. If user has made a mistake then it would be clear where the problem is.

Dim ws1 As Worksheet
For Each Rng In NameOfTheSheet
    On Error Resume Next


Set ws1 = Worksheets(Rng.Value)

If Err.Number <> 0 Then
Dim ThisSht As String
ThisSht = ws1.Name


'<------------WHAT TO ADD HERE???


MsgBox "Sheet "" " & ThisSht & " "" is missing"
        
        Exit Sub
    End If
Next Rng

Any feedback would be much appreciated.
Cheers