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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

So now it looks like this:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Any feedback would be much appreciated.
Cheers