Create name list from multiple sheets based on drop down selection

Hi Kelwea, see the attached sheet with macro. Hope that helps.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, k As Long, ws As Worksheet, ws1 As Worksheet
Set ws1 = Sheets("DET TOTAL")
k = 2

' Check if multiple cells changed, and if so, exit macro
If Target.Cells.Count > 1 Then Exit Sub

' Check if worksheet change involved cell L2
If Not Intersect(Target, ws1.Range("L2")) Is Nothing Then

    ' Clear contents of M2:Mx
    lastrow = ws1.Range("M" & Rows.Count).End(xlUp).Row
    ws1.Range("M2:M" & lastrow).ClearContents
    
    ' Assign column # based on drop-down selection (from layout of sheets HQ, FC, etc.)
    Select Case ws1.Range("L2").Value
        Case "ME"
            j = 2
        Case "TDY"
            j = 3
        Case "PCS"
            j = 4
        Case "Leave"
            j = 5
        Case "TW1"
            j = 6
        Case "TW2"
            j = 7
        Case "TW3"
            j = 8
        Case Else
            Exit Sub
    End Select
            
    ' Loop thru sheets
    For Each ws In ThisWorkbook.Worksheets
        
        ' Check for sheet name
        If ws.Name = "HQ" Or ws.Name = "FC" Or ws.Name = "LCHR" Or ws.Name = "EW" Or ws.Name = "SIG" Then
            
            ' Loop thru rows 3-50
            For i = 3 To 50
                
                ' Check if cell value in current row of specific column (j) equals 1
                If ws.Cells(i, j).Value = 1 Then
                
                    ' If so, set 'DET TOTAL'!Mk value to the value in that row, column A
                    ' where k starts at 2 and increments whenever a 1 is found
                    ws1.Cells(k, 13).Value = ws.Cells(i, 1).Value
                    k = k + 1
                    
                End If
            
            Next i
            
        End If
        
    Next ws
    
End If
    
End Sub