Heyho everyone
I've got the following situation: 2 different workbooks (WKB2 with multiple sheets / WKB1 with only 1 sheet).
I need to check in a range in WKB1 if a cell value is the same as in a range in WKB1. If yes, then the macro should copy the corresponding cell.offset 1&2 to the left, into a new column in WKB2 (where the value has been found). If nothing is found, it doesn't have to do anything.
I've got the following code....everything but the "copy-part" (red) works correctly.
I can't do it with functions, cause the data is changing weekly.
Thank you very much for your help!
I've got the following situation: 2 different workbooks (WKB2 with multiple sheets / WKB1 with only 1 sheet).
I need to check in a range in WKB1 if a cell value is the same as in a range in WKB1. If yes, then the macro should copy the corresponding cell.offset 1&2 to the left, into a new column in WKB2 (where the value has been found). If nothing is found, it doesn't have to do anything.
I've got the following code....everything but the "copy-part" (red) works correctly.
Code:
Dim wb1 As Workbook, wb2 As Workbook, lr As Long, c As Range, fn As Range
Set wb1 = Workbooks("WKB1.xlsm")
Set wb2 = Workbooks("WKB2.xlsm")
lr = wb2.Sheets("Data").Cells(Rows.Count, "I").End(xlUp).row
For Each c In Range("I2:I" & lr)
Set fn = wb1.Sheets("List").Range("M:M").Find(c.Value, , xlValues, xlWhole)
If Not gn Is Nothing Then
[COLOR=#ff0000]wb1.Sheets("List").Cells(c.row, Columns.Count).Cel.Offset(1, -1).Select
Selection.Copy
[/COLOR] wb2.Sheets("Data").Cells(c.row, Columns.Count).End(xlToLeft)(1, 2).PasteSpecial
Else
End If
Next
I can't do it with functions, cause the data is changing weekly.
Thank you very much for your help!