Shaolin
06-12-2009, 07:07 AM
I would like to compare first and last names (column A and B, respectively) on sheet 1 with first and last names (columns C and D, respectively) on sheet 2 and print the matches (entire row) on sheet 3, starting on row 2 down. Please help
Public Sub MDC_Usage()
'*Use this Macro to compare names from two worksheets and print result in another
'Declaring variables
Dim Sh1 As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet
Dim Nm As Range, c As Range, Tgt As Range
Dim Rng As Range
Set Sh1 = Sheets("Users")
Set Sh2 = Sheets("Usage")
Set Sh3 = Sheets("Final")
With Sh1
'Get list of last names from Sh1
Set Rng = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
'Search those names in Sh2
With Sh2.Columns(3)
For Each Nm In Rng
Set c = .Find(Nm, LookIn:=xlValues)
If Not c Is Nothing Then
Set Tgt = Sh3.Cells(Rows.Count, 1).End(xlUp).Offset(1)
'Copy names
c.Offset(, -2).Resize(, 70).Copy Tgt
End If
Next
End With
End With
End Sub
Public Sub MDC_Usage()
'*Use this Macro to compare names from two worksheets and print result in another
'Declaring variables
Dim Sh1 As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet
Dim Nm As Range, c As Range, Tgt As Range
Dim Rng As Range
Set Sh1 = Sheets("Users")
Set Sh2 = Sheets("Usage")
Set Sh3 = Sheets("Final")
With Sh1
'Get list of last names from Sh1
Set Rng = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
'Search those names in Sh2
With Sh2.Columns(3)
For Each Nm In Rng
Set c = .Find(Nm, LookIn:=xlValues)
If Not c Is Nothing Then
Set Tgt = Sh3.Cells(Rows.Count, 1).End(xlUp).Offset(1)
'Copy names
c.Offset(, -2).Resize(, 70).Copy Tgt
End If
Next
End With
End With
End Sub