Ah, I think I got the FILTER column wrong. Change to this:
Option Explicit
Private Sub Worksheet_Activate()
Dim ws As Worksheet, LR As Long
If MsgBox("Update this sheet?", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Application.ScreenUpdating = False
Range("A2", Range("D2").End(xlDown)).Clear 'clear existing summary
For Each ws In Worksheets
If ws.Name Like "Process - Act*" Then
ws.Range("B2:K2").AutoFilter
ws.Range("B2:K2").AutoFilter Field:=8, Criteria1:="Yes"
LR = ws.Range("I" & Rows.Count).End(xlUp).Row
If LR > 2 Then
ws.Range("B3:C" & LR & ",J3:J" & LR).SpecialCells(xlCellTypeVisible).Copy _
Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
ws.Range("C1").Copy
With Range(Range("A" & Rows.Count).End(xlUp).Offset(1, 0), _
Range("B" & Rows.Count).End(xlUp).Offset(0, -1))
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End If
ws.AutoFilterMode = False
End If
Next ws
Application.ScreenUpdating = True
End Sub
Remove the line of code in blue to make it occur automatically.
Bookmarks