Macro for compare between 2 workbooks and copy adjacent cells

[john-lau]

Hello

I get the macro from GS, it is about compare "account number" colum
(column K) in two workbooks, if it is matched account number, column L to
dat
will be copy from week1 unknown sor to week2 unknown sor.xls). May I ask ho
t
modify the macro, therefore, not all matched account number, copy data o
colum
L to N from week 1 excel to week 2 excel? For example, selection criteria i
column k ( column k, it has several choice: week 1 , week 2, week 3...

The code and file as following

https://rapidshare.com/files/461244589/week1_unknown_SOR_May.xl

https://rapidshare.com/files/461244592/week2_unknown_sor_May.xl


Sub CompareData_CustomerNewUpgrades(
Dim rngSource As Range, rngTarget As Range, rng As Rang
Dim lLastRow As Long, lRow As Long, i As Intege

Set rngTarget = Workbooks("week2 unknow
SOR.xls").Sheets("Unknown Customer Ne
Upgrade").Range("$l:$l"
Set rngSource = ThisWorkbook.Sheets("Unknown Customer Ne
Upgrade").Range("$l:$l"
lLastRow = rngSource.Rows(rngSource.Rows.Count).End(xlUp).Ro

For lRow = 1 To lLastRo
Set rng = rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole
If Not rng Is Nothing The
For i = 1 To 2: rng.Offset(, i) = rngSource.Cells(lRow).Offset(, i): Nex
End I
Nex
End Sub 'CompareData_CustomerNewUpgrades(

Thanks

 

[GS]

john-lau brought next idea :

Hello,

I get the macro from GS, it is about compare "account number" column
(column K) in two workbooks, if it is matched account number, column L to N
data
will be copy from week1 unknown sor to week2 unknown sor.xls). May I ask how
to
modify the macro, therefore, not all matched account number, copy data of
column
L to N from week 1 excel to week 2 excel? For example, selection criteria
is column k ( column k, it has several choice: week 1 , week 2, week 3...)

The code and file as following:

https://rapidshare.com/files/461244589/week1_unknown_SOR_May.xls

https://rapidshare.com/files/461244592/week2_unknown_sor_May.xls


Sub CompareData_CustomerNewUpgrades()
Dim rngSource As Range, rngTarget As Range, rng As Range
Dim lLastRow As Long, lRow As Long, i As Integer

Set rngTarget = Workbooks("week2 unknown
SOR.xls").Sheets("Unknown Customer New
Upgrade").Range("$l:$l")
Set rngSource = ThisWorkbook.Sheets("Unknown Customer New
Upgrade").Range("$l:$l")
lLastRow = rngSource.Rows(rngSource.Rows.Count).End(xlUp).Row

For lRow = 1 To lLastRow
Set rng = rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole)
If Not rng Is Nothing Then
For i = 1 To 2: rng.Offset(, i) = rngSource.Cells(lRow).Offset(, i): Next
End If
Next
End Sub 'CompareData_CustomerNewUpgrades()

Thanks

John,

First thing to note is this code is not the same code I sent you,
though the structure is the same.

To apply this to other ranges you need to change the rngTarget and
rngSource refs to match the columns you want to work with.

Also, the counter in the 'For i = 1 to 2...Next' loop (inside the
If...Then) needs to be changed to match the number of columns of data
you want to copy over to the other file. You state here that this is
cols L:N, which means the counter in the this loop needs to be
changed...

FROM '1 To 2'

TO '1 To 3'

...since you want to copy 3 cols of data.