1) You would have to go to the other sheets to change the values and come back, so SUMMARY sheet will always be accurate within the workbook itself.
2) This macro can replace the one in the workbook to fix the event to only the sheets named:
Option Explicit
Private Sub Worksheet_Activate()
Dim ws As Worksheet, LR As Long, NR As Long
Application.ScreenUpdating = False
Range("A10:AA" & Rows.Count).Clear 'clears existing data from row 10 down
NR = 10
For Each ws In Worksheets
Select Case ws.Name
Case "Bathroom", "Plumbing", "Tiling", "Plastering", "Labour-Sundries"
ws.Range("A:A").AutoFilter Field:=1, Criteria1:="1"
LR = ws.Range("A" & Rows.Count).End(xlUp).Row
If LR > 1 Then
ws.Range("A2:AA" & LR).SpecialCells(xlCellTypeVisible).Copy Range("A" & NR)
NR = Range("A" & Rows.Count).End(xlUp).Row + 1
End If
ws.AutoFilterMode = False
End Select
Next ws
Application.ScreenUpdating = True
End Sub
1) Right-click on the SUMMARY sheet tab
2) Select VIEW CODE
3) Delete the code in the window that appears
4) Paste in the code given above
5) Close the editor and save your workbook.
Bookmarks