VBA macro to search for names and then copy and insert

tthering

New Member
Joined
Nov 8, 2016
Messages
3
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:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows
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)

 

Watch MrExcel Video

Forum statistics

Threads
1,130,008
Messages
5,639,516
Members
417,093
Latest member
Citrusandsage

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top