CODEWITHSUNDEEP
vbaexcelms-office
Anti21
Anti21

Reputation: 880

Compare two different Excel Files and Copy data from one to another if conditions met

So I have two files. File1 and File2. Both have the same columns but one is filled and one is not.

What I want to do it if there is a match of name (suppose A1) from File2 in A row of File1 copy the rows Q,R and S to File2.

So if A1 (of File2) is found in A row of File1 (Suppose in A34) then I want to copy Q34,R34 and S34 of File1 to File2.

I think VLOOKUP would be required for the first part so I think that will need to be incorporated in the VBA.

Any help,idea or general direction where I should be heading for would be great.

Upvotes: 0

Views: 1695

Answers (1)

Xcelrate
Xcelrate

Reputation: 41

This should do what you are looking for. Place the macro in the workbook that contains all the filled columns, and change the path link to the second workbook file that you want to copy data over to. You may also need to change the sheet name from "Sheet1" if your sheet name is different.

Sub copyOnMatch()


Dim i As Long
Dim j As Long
Dim wbk1, wbk2 As Workbook

Set wbk1 = ThisWorkbook.Sheets("Sheet1")
WBK1Range = wbk1.Range("A" & Rows.Count).End(xlUp).Row
Set wbk2 = Workbooks.Open("Your File Path Here") 'Your Filepath here
WBK2Range = wbk2.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

    For j = 1 To WBK1Range
        For i = 1 To WBK2Range
             If wbk1.Cells(j, 1).Value = wbk2.Worksheets("Sheet1").Cells(i, 1).Value Then
                wbk2.Worksheets("Sheet1").Cells(j, 17).Value = wbk1.Cells(i, 17).Value 'Copies over Column Q 
                wbk2.Worksheets("Sheet1").Cells(j, 18).Value = wbk1.Cells(i, 18).Value 'Copies over Column R 
                wbk2.Worksheets("Sheet1").Cells(j, 19).Value = wbk1.Cells(i, 19).Value 'Copies over Column S
            Else
            End If
    Next i
Next j



End Sub

Upvotes: 1

Related Questions