VBA macro to search for names and then copy and insert

[tthering]

I have this code set up where I have it searching for certain names with autofilter on one sheet and then if the names are there, then it will copy all visible cells on the sheet and then paste it to a defined range on a different sheet. The code will recognize that the name I want it to search for are there and when I break out the code, it will copy the range but when I have it insert the copied range, it just inserts a blank row instead of the copied range. Been spinning my wheels trying to figure out why it's not working. Any help is appreciated.

Sub UpdateEmail()

Dim dstSheet As Worksheet
Dim srcSheet As Worksheet
Dim srcRng As Range
Dim lRow As Long

Set srcRng2 = Sheets("Paste").Range("G:G")

With ThisWorkbook
    Set srcSheet = .Sheets("Paste")
    Set dstSheet = .Sheets("Email")
End With


           
'John Update
Set rngJohn = srcRng2.Find("*" & "John" & "*")


If rngJohn Is Nothing Then
            MsgBox "No deals for John Doe"

            Application.DisplayAlerts = True
           
            Else
           
With srcSheet
lRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Rows(1).AutoFilter 7, "*" & "John" & "*"
Set srcRng = .Range(.Cells(2, 1), .Cells(lRow, 8))
srcRng.SpecialCells(xlCellTypeVisible).Copy


Range("JohnRow").Insert Shift:=xlDown

           

End With
Application.CutCopyMode = False
srcSheet.ShowAllData
End If

'Jane Update
Set rngJane = srcRng2.Find("*" & "Jane" & "*")


If rngJane Is Nothing Then
            MsgBox "No deals for Jane Doe"

            Application.DisplayAlerts = True
           
            Else
           
With srcSheet
lRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Rows(1).AutoFilter 7, "*" & "Jane" & "*"
Set srcRng = .Range(.Cells(2, 1), .Cells(lRow, 8))
srcRng.SpecialCells(xlCellTypeVisible).Copy


Range("JaneRow").Insert Shift:=xlDown

           
           
End With
Application.CutCopyMode = False
srcSheet.ShowAllData
End If

End Sub

 

[Zot]

I think you problem is wrong syntax of using Find function.

Set rngJohn = srcRng2.Find("*" & "John" & "*")

You should not use wildcard. It should be

Set rngJohn = srcRng2.Find("John", LookAt: xlPart)

Range.Find method (Excel)

Office VBA reference topic

docs.microsoft.com