Solved: Exclude Sheet names from list

[MRichmond]

Good day to everyone.

A two part challange today.

I have a workbook with a number of worksheets, the majority of which will have data in them relating to audits conducted. the number and name of the sheets will change over time.

I have code that activates when the Summary Sheet is selected that gets a list of all the sheets in the workbook and creates a list on this sheet.

So far so good

First challenge,

There are a number of tabs that I dont wish to be in this list on the summary tab, but they do have to be in the workbook. The sheets to be exluded are listed on a hidden sheet ("Lists"), in a named range "Exclude".

How can I exclude theses sheets from my list on the summary tab.

Second challenge,

For each of the sheets that make it onto the summary tab list, in column B I need the audit result. This will always come from the same place on the audit sheets (in the test case it is cell "I2"). How do I combine a sheet name (which will change) with the cell reference (which will always be the same), to give me what I am looking for (see example sheet "Summary Two").

Thanks in advance for your help

[p45cal]

Private Sub Worksheet_Activate()
Intersect(Range("A:B"), UsedRange.Offset(1)).ClearContents
For Each sht In ThisWorkbook.Sheets
If Sheets("Lists").Range("Exclude").Find(sht.Name, lookat:=xlWhole, LookIn:=xlFormulas) Is Nothing Then
Set lr = Cells(Rows.Count, 1).End(xlUp).Offset(1)
lr.Value = sht.Name
lr.Offset(, 1).Value = sht.Range("I2").Value
End If
Next sht
End Sub

[MRichmond]

Thanks p45cal,

That works great, just what I was looking for.

[TeacherCF]

Thank you for this! It has really helped me.
I'm a teacher, new to VBA and I am using it to create a training log for teacher training needs. The index pulls through the worksheet names (teacher names). I wondered if it is possible to start the index on a particular cell on a worksheet e.g. B3, as I have other data that I would like on this worksheet.

Many thanks

[p45cal]

Private Sub Worksheet_Activate()
Set Destn = Range("B3")
For Each sht In ThisWorkbook.Sheets
If Sheets("Lists").Range("Exclude").Find(sht.Name, lookat:=xlWhole, LookIn:=xlFormulas) Is Nothing Then
Destn.Value = sht.Name
Destn.Offset(, 1).Value = sht.Range("I2").Value
Set Destn = Destn.Offset(1)
End If
Next sht
End Sub
No clearing is done so you will need to delete old information from the sheet first.

[TeacherCF]

Private Sub Worksheet_Activate()
Set Destn = Range("B3")
For Each sht In ThisWorkbook.Sheets
If Sheets("Lists").Range("Exclude").Find(sht.Name, lookat:=xlWhole, LookIn:=xlFormulas) Is Nothing Then
Destn.Value = sht.Name
Destn.Offset(, 1).Value = sht.Range("I2").Value
Set Destn = Destn.Offset(1)
End If
Next sht
End Sub
No clearing is done so you will need to delete old information from the sheet first.

Thank you so much, really grateful for your time and expertise