Finding a value in a range in a for/next loop...

[BLaCKouT]

Hello all,

Apologies in advance if this is very basic and has been asked a hundred times, but I can't seem to find a solution I can adapt for what I need

I've got a workbook with several worksheets in it. As time goes on, new sheets will be added, and some deleted. I've cobbled together the following macro to list the current worksheets on a separate worksheet called "List"

Sub ListMyWorkSheets()
Dim WorksheetName As Worksheet
Dim RowNumber As Integer
RowNumber = 2
Sheets("List").Range("A2:A99").Clear
For Each WorksheetName In Worksheets
Sheets("List").Cells(RowNumber, 1) = WorksheetName.Name
RowNumber = RowNumber + 1
Next WorksheetName
End Sub

On each of the sheets, data exists in the same locations (it's essentially a supplier performance database with a sheet for each supplier), and I can use this list to pull figures from each sheet for comparison. With this in mind, I'm going to be creating some summary sheets. I'd like to exclude these sheets from the listing process.

To this end, I've created a list in 'List'!F2:F20 of exceptions that I don't want to be included in the list the macro creates. I think it's a case of putting a find query in the middle of the for/next loop, and nexting it prematurely if WorksheetName appears in F2:F20, but I can't seem to make it work.

Can you tell me the most efficient way of achieving this?

Many thanks in advance...
B.

 

[VoG]

Try

Sub ListMyWorkSheets()
Dim WorksheetName As Worksheet
Dim RowNumber As Integer
RowNumber = 2
Sheets("List").Range("A2:A99").Clear
For Each WorksheetName In Worksheets
    If IsError(Application.Match(WorksheetName.Name, Sheets("List").Range("F2:F20"), 0)) Then
        Sheets("List").Cells(RowNumber, 1) = WorksheetName.Name
        RowNumber = RowNumber + 1
    End If
Next WorksheetName
End Sub

 

[BLaCKouT]

You are awesome and I think I love you.

Seriously, thank you for that. It's perfect and is exactly what I was looking for.