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
Bookmarks