Cannot delete the cells with old data in them to make way for new data

Question

In sheet 2 of my workbook, I have names of employees, the dates they came into work, the shifts they worked, and absenteeism. In sheet 1 of my code, I have a lookup sheet where I intend for the employee's name to be typed into a cell and to show all the dates and this person worked, along with the shift and absenteeism into the lookup sheet. I've tried a vriaty of things, but this is my current code:

Private Sub worksheet_change(ByVal Target As Range)
    Dim Lookup As Worksheet
    Dim Data As Worksheet
    Dim LastRow As Long
    Dim V As Range
  
    Set Lookup = ThisWorkbook.Worksheets("Lookup")
    Set Data = ThisWorkbook.Worksheets("Data")
    Set V = Lookup.Range("A1:A2")
    
    LastRow = Data.Cells(Rows.Count, "A").End(xlUp).Row
    LookupCounter = 2
    
    For i = 2 To LastRow
    
        If Intersect(V, Target) Is Nothing Then
            Lookup.Range("B2:C2000").Delete
        ElseIf Lookup.Range("A2") = Data.Cells(i, 2) Then
                Lookup.Cells(LookupCounter, 2).Value = Data.Cells(i, 1)
                Lookup.Range("B2:B2000").NumberFormat = "mm/dd/yyyy"
                Lookup.Cells(LookupCounter, 3).Value = Data.Cells(i, 9)
                LookupCounter = LookupCounter + 1
            End If
    Next i
        
End Sub

My intention is for when a new name is typed, this clears the info from the columns of the lookup page, and inputs the new data for the new name. The second part of my code where I match the names to the dates works, but I am struggling with the clearing function. What can I do to fix it?

Answer 1

Option Explicit

Private Sub worksheet_change(ByVal Target As Range)
    
    Dim Lookup As Worksheet, Data As Worksheet
    Dim LastRow As Long, LookupCounter As Long, i As Long
  
    With ThisWorkbook
        Set Lookup = .Worksheets("Lookup")
        Set Data = .Worksheets("Data")
    End With

    If Intersect(Lookup.Range("A1:A2"), Target) Is Nothing Then
        Exit Sub
    Else
        ' clear sheet
        Lookup.Range("B2:C2000").Delete
        LastRow = Data.Cells(Rows.Count, "A").End(xlUp).Row
        LookupCounter = 2
        
        ' get data
        For i = 2 To LastRow
           If Data.Cells(i, 2) = Lookup.Range("A2") Then
               Lookup.Cells(LookupCounter, 2).Value = Data.Cells(i, 1)
               Lookup.Cells(LookupCounter, 3).Value = Data.Cells(i, 9)
               LookupCounter = LookupCounter + 1
           End If
        Next
        Lookup.Range("B2:B2000").NumberFormat = "mm/dd/yyyy"
    End If
        
End Sub