Using a Defined Name (Dynamic List) in Code

[vikrampnz]

Hello

I have a piece of code which works well for its intended purpose and has part where certain worksheet names are specified which are to be excluded for the list that is made by using that code. (See the sample below)

Dim xSheet As Worksheet
Dim xRow As Integer
Dim calcState As Long
Dim scrUpdateState As Long

For Each xSheet In Application.Worksheets
If IsError(Application.Match(xSheet.Name, _
Array(Me.Name, "Reports", "Trial List", "Original Template"), 0)) Then
xRow = xRow + 1

In the above code, "Reports", "Trial List", "Original Template" are the three worksheets that will be excluded from the displayed list. I have created a table ("Table2") and am adding the names of worksheets to be excluded in that table in the column named "Ignore_L", as its much easier for me to add 3-4 more names in the table than to add in those sheetnames in the code directly every time. Purpose of inserting table was that the range automatically extends everytime a new name is added to the list. I have defined a name for this column in the table as "Ignore_List".

I have tried referencing the Defined Name "Ignore_List" in my code with no luck. It either gives an error or doesn't work.

Would appreciate any help from the group !

Cheers

 

[vikrampnz]

Thanks a lot Peter_SSs.

I used this code you gave and it worked like a dream. !!

If IsError(Application.Match(xSheet.Name, Sheets("DataMaster").Range("Ignore_List[IGNORE]"), 0)) Then

Sorry for confusing you with other technicalities in the code. I am new to VBA and wasnt aware of its impact. Thanks again all your help !!

Cheers

 

[Peter_SSs]

You're welcome. Glad we got it sorted in the end.