VBA to find match of cell value on two different Spreadsheet and then copy adjacent cell (value and format)

[lpchretien]

Hello,

I have two spreadsheets named New.xlsx and Old.xlsx. I'm looking for a VBA code that compare each column T values (unique ID) in New.xlsx with values in column T (unique ID) in Old.xlsx. When there is a match, it then copy the value and cell format (e.g. cell color) of the cell in column A of the Old.xlsx to the cell in column A in New.xlsx.

I found out this thread (VBA To find match of cell value and copy adjacent cell when match found) which work fine with 2 different Sheets in the same Excel document and to copy only cell value, but I have some difficulty to use it with 2 differents Excel files and to copy cell format.

Thanks for your help

 

[jolivanes]

Re: "two spreadsheets named New.xlsx and Old.xlsx"
I assume you mean 2 workbooks? Or not?
Are both workbooks open when you run the macro?
Which workbook should have the macro as you have both workbooks as non macro workbooks (.xlsx)
Macro enabled workbooks have an .xlsm extension
What are the Sheet Names where you copy from and paste into?

 

[jolivanes]

Try this (Both Workbooks to be open).
Change wb1, wb2, sh1 and sh2 to meaningful names for you

Sub Copy_Between_Workbooks()
Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim c As Range
Set wb1 = ThisWorkbook    '<----- Workbook with the macro in it and where you paste into
Set wb2 = Workbooks("The Other Workbook Name.xlsx")    '<----- Change as required. Workbook where you copy from
Set sh1 = wb1.Worksheets("Sheet1")    '<----- Change as required
Set sh2 = wb2.Worksheets("Sheet1")    '<----- Change as required
    For Each c In sh1.Range("T2:T" & sh1.Cells(Rows.Count, 20).End(xlUp).Row)
        If Not IsError(Application.Match(c.Value, sh2.Columns(20), 0)) Then
            sh2.Columns(20).Find(c.Value, , , 1).Offset(, -19).Copy c.Offset(, -19)
        End If
    Next c
End Sub