Hi All,
I have excel file named as A.xls & B.xls
I get A.xls in morning and B.xls in Evening.( day 1 )
Once i get B.xls I need to compare this workbook with A.xls
ie., Col A has some codes in all workbooks that will repeated some
times.
So, If i know which one is repeated i will prioritize the work.
Here is an example,
A.xls

Col A
ABC45
IND45
AUD45
CZK47

B.xls

Col A
ABC47
IND45
AUD48
AUD46
CZK47
IN45

I have created tiny macro .. pls see below
please create two workbook named as a.xls & b.xls
Col A put some letter or numbers in both ( a.xls & b.xls - Sheet 2 )
which starts from A2 and end at A10 ( this is an example )
in b.xls put this macro and run it from b.xls .....

Sub matcher()
Dim str As String
Range("B2").Select
a = ActiveWorkbook.Name
Application.Dialogs(xlDialogOpen).Show (str)
b = ActiveWorkbook.Name
Workbooks(a).Activate
Do Until ActiveCell.Offset(0, -1).Value = ""
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=[a.xls]Sheet2!R2C1:R10C1,""repeated"","""")"
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(1, 0).Select
Range("B2:B10").Select
Selection.Copy
Range("b2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
End Sub

this is running fine only when i know the file name and sheet
name ...
this file name and sheet name changes from day to day.
As I inserted file name and sheet name in formula.

Tomorrow I will receive C.xls then I need to compare with B.xls
if i received D.xls then I will compare with C.xls...... goes
on ,,,,,,

Try this

Sub matcher()
Dim str As String

Set BkASht = ThisWorkbook.ActiveSheet

Application.Dialogs(xlDialogOpen).Show (str)
Set BKB = ActiveWorkbook
Set BkBSht = BKB.ActiveSheet

With BkASht
RowCount = 2
Do While .Range("A" & RowCount) = ""
Data = .Range("A" & RowCount)
Set c = BkBSht.Columns("A").Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then
.Range("B" & RowCount) = "repeated"
End If
RowCount = RowCount + 1
Loop
End With
End Sub


" wrote:

Hi All,
I have excel file named as A.xls & B.xls
I get A.xls in morning and B.xls in Evening.( day 1 )
Once i get B.xls I need to compare this workbook with A.xls
ie., Col A has some codes in all workbooks that will repeated some
times.
So, If i know which one is repeated i will prioritize the work.
Here is an example,
A.xls

Col A
ABC45
IND45
AUD45
CZK47

B.xls

Col A
ABC47
IND45
AUD48
AUD46
CZK47
IN45

I have created tiny macro .. pls see below
please create two workbook named as a.xls & b.xls
Col A put some letter or numbers in both ( a.xls & b.xls - Sheet 2 )
which starts from A2 and end at A10 ( this is an example )
in b.xls put this macro and run it from b.xls .....

Sub matcher()
Dim str As String
Range("B2").Select
a = ActiveWorkbook.Name
Application.Dialogs(xlDialogOpen).Show (str)
b = ActiveWorkbook.Name
Workbooks(a).Activate
Do Until ActiveCell.Offset(0, -1).Value = ""
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=[a.xls]Sheet2!R2C1:R10C1,""repeated"","""")"
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(1, 0).Select
Range("B2:B10").Select
Selection.Copy
Range("b2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
End Sub

this is running fine only when i know the file name and sheet
name ...
this file name and sheet name changes from day to day.
As I inserted file name and sheet name in formula.

Tomorrow I will receive C.xls then I need to compare with B.xls
if i received D.xls then I will compare with C.xls...... goes
on ,,,,,,