How to compare data in a spreadsheet and paste matched values in another sheet

[Michael1974]

Hello,

I have a spreadsheet with the inventory of several parts. I need to create a macro that will compare the information between the original spreadsheet and a separate set of 3 columns.

I am comparing 3 criterias Item #, Locations and Quantity row by row. When and if the information between the set of columns and the original spreasheet match, I want to copy the information matched and paste it in another spreadsheet.

Can someone help? If my question is not clear, please let me know in order for me to clarify.

Thanks,

Mike

[Michael1974]

is somebody here? Can someone help?

[Yongle]

Yes there are quite a few of us here.
Looking at the length of time between post#01 and post#02 you seem perhaps a little impatient for a reply.

Please clarify the following
1. Are the three comparison columns in the target workbook OR in a separate sheet in the original workbook OR in another workbook?
2. Are the 3 columns adjacent to each other?
3. When you copy are you wanting to copy only those 3 columns or are you wanting to copy the entire row?
thanks

[Michael1974]

Please see below the answer to your questions:

1) Yes there are 3 comparison columns in the same target workbook

2) Yes there are 3 adjacent columns to each other

3) Only want to copy and paste those 3 columns that match to another sheet

[Yongle]

Below is one way of approaching your requirement.

Save the attached 2 test files to any folder and amend one line in the code (which is in the Target file) changing D:\Documents to your chosen folder and then open up the Target file and run the macro. The Original file is opened and closed within the macro.
I suggest you alter some of the values in sheet1 in both files (perhaps pasting in some of your own real values) and satisfy yourself that the macro is doing what you want.

After doing that then try pasting this code into your target file and changing what you need to change which will be at least:
- file path and name
- the 3 sheet names

My macro assumes that the data is held in Columns A, B & C in both files and will be pasted to the same columns. If that is not the case see below ***


Option Explicit

Sub Match_Copy()
'declare variables
Dim rA As Integer, cA As Integer
Dim rB As Integer, cB As Integer
Dim LastRowA As Long, LastRowB As Long, NextRowC As Long
Dim wsA As Worksheet, wsB As Worksheet, wsC As Worksheet
'target workbook sheets and last row
Set wsC = ActiveWorkbook.Sheets("Sheet2") ' << sheet to paste matched values
Set wsB = ActiveWorkbook.Sheets("Sheet1") ' << sheet containing compare values
LastRowB = wsB.Cells(Rows.Count, "A").End(xlUp).Row
'open original workbook
Workbooks.Open ("d:\documents\ILQ_original.xlsx") ' << put in file name including full path
'original workbook sheet and last row
Set wsA = ActiveWorkbook.Sheets("Sheet1") ' << sheet in original workbook
LastRowA = wsA.Cells(Rows.Count, "A").End(xlUp).Row
'compare the 3 columns in the 2 workbooks and copy/paste to sheet2 if a match is found
For rA = 2 To LastRowA
For rB = 2 To LastRowB
If wsA.Range("A" & rA) = wsB.Range("A" & rB) And wsA.Range("B" & rA) = wsB.Range("B" & rB) And wsA.Range("C" & rA) = wsB.Range("C" & rB) Then
wsA.Range("A" & rA & ":C" & rA).Copy
NextRowC = wsC.Range("A1048576").End(xlUp).Offset(1, 0).Row
wsC.Range("A" & NextRowC & ":C" & NextRowC).PasteSpecial Paste:=xlPasteAll
Else
End If
Next rB
Next rA
'close original workbook without saving
Workbooks("ILQ_original.xlsx").Close False
End Sub

*** If data is not held in Columns A, B & C in either or both files, or you do not want it pasting to those columns, you will need to change some or all of the letters in red.


If wsA.Range("A" & rA) = wsB.Range("A" & rB) And wsA.Range("B" & rA) = wsB.Range("B" & rB) And wsA.Range("C" & rA) = wsB.Range("C" & rB) Then
wsA.Range("A" & rA & ":C" & rA).Copy
NextRowC = wsC.Range("A1048576").End(xlUp).Offset(1, 0).Row
wsC.Range("A" & NextRowC & ":C" & NextRowC).PasteSpecial

[Michael1974]

Thanks a lot. I will try it and will let you know if works for me

[Yongle]

Hi @Michael1974 (http://www.vbaexpress.com/forum/member.php?56204-Michael1974)

After confirming that the macro works fine, please come back and go to Thread Tools at the top of the thread and mark the thread as solved.

Thanks Yon