Compare and Print with VBA HELP!!

[Shaolin]

I want to compare two columns from two excel spreadsheets and print out info from another column.
So let's say spreadsheet 1 is called ABC.xls and the second one is called DEF.xls

In ABC.xls worksheet "WS3" Column G is first name and Column H is last name and in DEF.xls and worksheet "WS5" Column E is First Name and Column F is last Name.

I want to compare the entire spreadsheet where the first name and last name is the same. When this occurs I want to print out column A, E and F on the corresponding row in DEF.xls to a new spreadsheet in Column A, B and C.

[mdmackillop]

Can you post a sample?

[Shaolin]

I posted a spreadsheet where:

Worksheet ABC has a list of names, first and last, and worksheet DEF has a list of names, first and last, AND usernames.

I am currently struggling to find a way to compare the list of names from those two worksheets (ABC and DEF) and whenever there is a match I want it to be displayed in worksheet GHI along with its corresponding username.

It is apparent to me that the steps are as follows (not sure how to execute):

1. a loop which takes the last name from worksheet ABC and looks through all of the last names in worksheet DEF and

2. when it matches look at the first name and if the first names matches copy and past the first and last name in worksheet GHI with it's username

3. if it does not match, continue with the loop

4. Take the next last name in worksheet ABC and repeat step 1.

There is way too many names for me to do this manually. It needs to be done later today.

[mdmackillop]

[VBA]Option Explicit

Sub CopyNames()
Dim Sh1 As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet
Dim LCel As Range, Nm As Range, c As Range, Tgt As Range
Dim Rng As Range, firstaddress As String

Set Sh1 = Sheets("ABC")
Set Sh2 = Sheets("DEF")
Set Sh3 = Sheets("GHI")

With Sh1
'Get list of surnames ABC
Set Rng = Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
'Seach names in DEF
With Sh2.Columns(5)
For Each Nm In Rng
Set c = .Find(Nm, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
'Check first names
If Nm.Offset(, -1) = c.Offset(, -1) Then
'Get next vacant cell
Set Tgt = Sh3.Cells(Rows.Count, 1).End(xlUp).Offset(1)
'Copy names from DEF
c.Offset(, -1).Resize(, 3).Copy Tgt
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
Next
End With
End With
End Sub
[/VBA]

[Shaolin]

wow, thanks a bunch.

[Shaolin]

What does "subscript out of range" error refer to?

[Shaolin]

nevermind I got it.

[Shaolin]

One more thing, what if I wanted to keep everything the same, but print the entire row onto sheet GHI????

[Shaolin]

Nevermind I got it. This dummies book is good

[mdmackillop]

Hi Shaolin,
Feel free to post your solution/development for the benefit of others.
Regards
MD

[Shaolin]

Hi Shaolin,
Feel free to post your solution/development for the benefit of others.
Regards
MD

[vba]

Set Tgt = Sh3.Cells(Rows.Count, 1).End(xlUp).Offset(1)
'Copy names from DEF
c.Offset(, -5).Resize(, 50).Copy Tgt
[/vba]

Since I wanted to start to copy the range you declared (Tgt) from 5 cells to the left and 50 cells to the right I changed two numbers. That was about it. I didn't know what offset was until now.