Macro Stops Empty cell

[Plukey]

After I add new rows to table, the code will not work on target cell unless it has something in it. I'm using a Vlookup from another sheet source to automatically add the letter to Column "P"

Option Explicit
Sub Macro1()
 Application.EnableEvents = False
    Dim lngLastRow As Long
    Dim wsOutput   As Worksheet
    Dim wsSource   As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wsOutput = Sheets("DISPO")
    Set wsSource = Sheets("KEY")
    lngLastRow = wsOutput.Range("P:P").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With wsOutput
        
        With .Range("P2:P" & lngLastRow)
            .Formula = "=VLOOKUP(J2,'" & CStr(wsSource.Name) & "'!A:B,2,FALSE)"
            .Value = .Value
        End With
        
    End With
                  
    Set wsOutput = Nothing
    Set wsSource = Nothing
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.EnableEvents = True
End Sub

 

[Plukey]

Just noticed another issue, it doesn't work on filtered rows...any help would be much appreciated!