Throw away/delete the code I gave you earlier.  To do it this way, we need to
approach it in a different manner.

To put the code where it needs to be, open the workbook and select the
worksheet that you want the list to appear on and right-click it's name tab
and choose [View Code] from the list that appears.  Copy the code below and
paste it into the module presented to you.  Change either or both of the two
Const values as needed.  

Now each time the sheet is selected it will examine cell M5 on all sheets
and list them in column A on this sheet beginning at A1.  The list is
refreshed each time you choose this sheet.

Private Sub Worksheet_Activate()
 Const baseCellAddress = "A1" ' change
 Const pendingCell = "M5" ' may change
 Dim anySheet As Worksheet
 Dim testRange As Range
 Dim baseCell As Range
 Dim rowOffset As Long
 
 Set baseCell = ActiveSheet.Range(baseCellAddress)
 Columns("A:A").Clear ' clear out old results
 For Each anySheet In Worksheets
   Set testRange = anySheet.Range(pendingCell)
   If UCase(Trim(testRange)) = "PENDING" Then
     baseCell.Offset(rowOffset, 0) = _
      anySheet.Name
     rowOffset = rowOffset + 1
   End If
 Next
 Set testRange = Nothing
End Sub