Hi Barb,

Everyday I receive two excel files only col A has some codes - those
are in sheet2 most of the time. It may come in sheet1 or sheet3.

col A - a.xls Col A - b.xls
123 456
456 abc
789 jhi
abc
efg

I have to run a macro for b.xls here in col A there are 3 codes which
need to match with a.xls Col A.
We can easily make out only 456 & abc are matching. next 456 i need a
word "repeated" as it is
matching with a.xls Col A......

This is what i want. ...



On Dec 15, 2:16*am, Barb Reinhardt
wrote:
Here's a start. * You'll need to provide more information for someone to
assist further, I suspect.

Option Explicit
Sub Test()
Dim aWB As Workbook
Dim bWB As Workbook
Dim aWS As Worksheet
Dim bWS As Worksheet

Set aWB = OpenWorkbook("First")
Set bWB = OpenWorkbook("Second")

'Need code to select the worksheets
' *I know this isn't what you're asking for, but it's a start.
' *Maybe someone else can assist
' *I'd probably set a codename for the worksheet
' *and reference that

Set aWS = aWB.Worksheets(1)
Set bWS = bWB.Worksheets(1)

'I'm not sure which columns you want to match
' *So you'll need to provide more information
' *on that

End Sub

Function OpenWorkbook(myTitle As String) As Workbook
Dim sFile As String
Dim ShortName As String

With Application.FileDialog(msoFileDialogFilePicker)
* * .AllowMultiSelect = False
* * .Title = "Find and open the" & myTitle & " workbook."
* * If .Show = False Then
* * * * MsgBox ("You cancelled opening the " & myTitle & " file. *Execution
ending.")
* * * * End
* * End If
* * sFile = .SelectedItems
End With

ShortName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))

Set OpenWorkbook = Nothing
On Error Resume Next
Set OpenWorkbook = Workbooks(ShortName)

If OpenWorkbook Is Nothing Then
* * Set OpenWorkbook = Workbooks.Open(Filename:=sFile)
End If
End Function

'UNTESTED.

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



" 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

Compare B.xls with A.xls - *there are some repeated codes.
i.e, IND45 & CZK47
i need the word "repeated" if it matches with A.xls in col B for B.xls

What I need ?
I will open B.xls and if i run the macro open dialog will come then I
will select
the A.xls and it need ask which sheet u need match, and then I will
select the sheet.
Then it will match.

File are not constant ...
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 ,,,,,,

Thanks a lot in advance.- Hide quoted text -

- Show quoted text -