Excel 2007 : Auto select and copy rows containing a cell with a specific value

Was working on this when Dave posted - oh well, can't let effort go to waste.
The attached automates Advanced Filter and population of the validation list and uses the worksheet activate and change events.

Option Explicit

Private Sub Worksheet_Activate()
    Dim lastrow As Long, rng1 As Range
    Dim BrandList As String, bottomrow As Long, rngVal_list As Range
    
    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    bottomrow = Me.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng1 = Sheet1.Range("B3:B" & lastrow)
    
    Application.ScreenUpdating = False
    
    With Me
    
        .Range("A7:A" & Rows.Count).End(xlUp).ClearContents
        
        rng1.AdvancedFilter xlFilterCopy, , .Range("A7"), True
        
        Set rngVal_list = .Range("A8:A" & bottomrow)
        
        ThisWorkbook.Names.Add Name:="BrandList", RefersTo:=rngVal_list
        
        .Range("C4").Validation.Delete
        
        .Range("C4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=BrandList"
    End With
    
    Set rng1 = Nothing
    
    Application.ScreenUpdating = True
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lastrow As Long
    
    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

    Set rng2 = Sheet1.Range("A3:K" & lastrow)
    
    Application.ScreenUpdating = False
    
    On Error Resume Next

    With Me
        If Not Intersect(ActiveCell, .Range("C4")) Is Nothing Then
            .Range("B7").CurrentRegion.Offset(, 1).ClearContents
            rng2.AdvancedFilter xlFilterCopy, .Range("B1:B2"), .Range("B7")
        End If
    End With
    
    Application.ScreenUpdating = False
    
    Set rng2 = Nothing
End Sub