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.
VBA Code:
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
Last edited by a moderator: