Solved: Dynamic List of Worksheet Names based on criteria

[akamax_power]

I've been searching the forum for a while now and can't seem to find what I'm looking for. Is it possible to make a dynamic list of worksheet names of all worksheets that have "TPL" in cell K2? I'd also like the list to automatically update whenever a new sheet is added that matches the cell criteria.

[mdmackillop]

Are you adding a sheet that contains TPL, or writing TPL into an existing sheet?

[akamax_power]

One sheet is used as a template that has TPL in the cell. Then it's copied as a new sheet

[mdmackillop]

Put the following in ThisWorkbook module
[VBA]
Private Sub Workbook_Open()
Listing
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Listing
End Sub

Sub Listing()
Dim sht As Worksheet
For Each sht In Sheets
If sht.Range("K2") = "TPL" Then
i = i + 1
Sheets("Sheet1").Cells(i, 1) = sht.Name '<--Change to suit
End If
Next
End Sub
[/VBA]

[akamax_power]

That's what I needed