Maybe create a macro that adds that formula, does the filter and copy and
paste??

I made some assumptions:

The cashx worksheet was named Sheet1. The data on that sheet was limited to
c2:c9999 (big enough to not worry).

I used Column C to find the last used row in the new cash file (cashy??).

I had one header row in my new file (cashy).

I could do whatever I wanted with column H.

If those aren't valid, you can modify the following.

Option Explicit
Sub testme01()

'=IF(ISNUMBER(MATCH(C1,[cashx.xls]Sheet1!$C$2:$C$9999,0)),"Old","New")

Dim wks As Worksheet
Dim cashxWks As Worksheet
Dim rng As Range
Dim rngF As Range
Dim newWks As Worksheet

Set cashxWks = Nothing
On Error Resume Next
'fix the worksheet name
Set cashxWks = Workbooks("cashx.xls").Worksheets("sheet1")
On Error GoTo 0

If cashxWks Is Nothing Then
MsgBox "please open current cashx workbook" & vbLf & "And try again"
Exit Sub
End If

Set wks = ActiveSheet 'workbooks("cashy.xls").worksheets("sheet1") ??

With wks
.AutoFilterMode = False 'turn off autofilter
.Range("H1").Value = "New/Old"
Set rng = .Range("h2:h" & .Cells(.Rows.Count, "C").End(xlUp).Row)
rng.Formula _
= "=IF(ISNUMBER(MATCH(C2,[cashx.xls]Sheet1!$C$2:$C$9999,0))" _
& ",""Old"",""New"")"
.Range("H:H").AutoFilter field:=1, Criteria1:="new"

Set rng = .AutoFilter.Range
On Error Resume Next
Set rngF = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rngF.Cells.Count = 1 Then
MsgBox "No new values!"
Else
Set newWks = Worksheets.Add
rngF.EntireRow.Copy _
Destination:=newWks.Range("a1")
End If

'clean up
.AutoFilterMode = False
.Range("H:H").EntireColumn.Delete

End With

End Sub




Pete wrote:

Dave,

I could also do a simple iserror lookup, but i have to think of the
users. They have very limited experience of Excel (and they don't
have the greatest of learning curves). Therefore, I would like to
make this as simple as possible i.e. at the press of a button/add-in.

Appreciate any help!

--

Dave Peterson