Check names on every sheet

In a large (and constantly changing) Excel file I would like to check which worksheets do have the named range “Obj_Nr”. Next step would be to run another macro only on these worksheets.

I am trying to get a list of worksheet numbers which meet the required name (like sheet 2, 3, 5 and 8).

I tried the following code:

Please Login or Register  to view this content.

First I thought the problem was this NamedRange always has the worksheet name in front of it (like ‘Sheet1’!Obj_Nr and ‘Sheet2’!Obj_Nr). This would hopefully be solved by the * in front of the search term "*Obj_Nr".

Unfortunately the macro is stuck at this line:

Please Login or Register  to view this content.

Does anybody know what I am doing wrong here?

Thank you in advance.
Erik
(The Netherlands)

Thank you for advice. This indeed works to help me identifying the worksheets which contain this name. But I don't know how to make a list of these worksheets in order to use the numbers in another macro.
Is is possible to 'store' the hits in the macro's memory?

Thank you very much! When I run this code step-by-step with F8 I can see the result is perfect. In the code I can see GetSheetNumbers = "7,8,9" when I hold the mouse above it.

In another macro I followed your advice with the split command.

Please Login or Register  to view this content.

Unfortunately now I am stuck at the second line. Do you know my error here?

Thank you in advance