Add row below if value is found on another sheet

[tropics123]

Hi, Thank you in advance for your help. Is there a macro to add a row below if the value on the current sheet is found on another sheet?

Example:

Sheet 1 has these names in column B:
James
Bob
Mary

If those names appear on Sheet 2, then add a row below each of those names on Sheet1.

 

[JoeMo]

Assuming your names start in sheet1, cell B2 (change to suit your layout):

Sub tropics123()
Dim c As Range, fnd As Variant, fAdr As String
Application.ScreenUpdating = False
For Each c In Sheets("Sheet1").Range("B2:B" & Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row)
    If c.Value <> "" Then
        Set fnd = Sheets("Sheet2").Cells.Find(what:=c.Value, lookat:=xlPart, LookIn:=xlValues, MatchCase:=True)
        If Not fnd Is Nothing Then
            Sheets("Sheet1").Cells(c.Row + 1, "B").EntireRow.Insert
        End If
    End If
Next c
Application.ScreenUpdating = True
End Sub

 

[tropics123]

This works perfectly! Pure genius! Thank you!!!

 

[JoeMo]

This works perfectly! Pure genius! Thank you!!!

You are welcome - thanks for the reply.