Macro for compare between 2 workbooks and copy adjacent cells
 

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/4612445...wn_SOR_May.xls

https://rapidshare.com/files/4612445...wn_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

Macro for compare between 2 workbooks and copy adjacent cells
 

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/4612445...wn_SOR_May.xls

https://rapidshare.com/files/4612445...wn_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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc