No, you need to put it in a procedu

Sub Tester1()
Dim bk1 as workbook, bk2 as workbook
Dim sh as worksheet, cell as range, rng as Range
Dim rng1 as Range, res as Variant
set bk1 = Workbooks("All_data.xls")
set bk2 = workbooks("Reports.xls")
set rng = bk1.Range(bk1.Cells(2,1),bk1.Cells(2,1).End(xldown ))
for each cell in rng
set sh = Bk2.Worksheets(cell.offset(0,1).value)
set rng1 = sh.Range(sh.cells(2,"H"),sh.Cells(2,"H").End(xldow n))
res = Application.Match(clng(cell.offset(0,7)),rng1,0)
if iserror(res) then
cell.Entirerow.copy _
Destination:=rng1.offset( _
rng1.rows.count,0).Resize(1,1)
end if
Next
End Sub

I didn't put it in a procedure because I don't want to create the impression
I bench tested it - I did not, so it may contain typos but represents an
approach. It is assumed you can fine tune it to meet your needs.

--
Regards,
Tom Ogilvy



"Martyn" wrote in message
...
Hi Tom,
I used the code in the "Reports.xls" file with the All_data.xls file open
but
Received an Compile error "Invalid Outside Procedure" for the line
Set bk1 = Workbooks("All_data.xls")
Should the code reside on a seperate file?


"Tom Ogilvy" wrote in message
...
it should go something like this.

Dim bk1 as workbook, bk2 as workbook
Dim sh as worksheet, cell as range, rng as Range
Dim rng1 as Range, res as Variant
set bk1 = Workbooks("All_data.xls")
set bk2 = workbooks("Reports.xls")
set rng = bk1.Range(bk1.Cells(2,1),bk1.Cells(2,1).End(xldown ))
for each cell in rng
set sh = Bk2.Worksheets(cell.offset(0,1).value)
set rng1 = sh.Range(sh.cells(2,"H"),sh.Cells(2,"H").End(xldow n))
res = Application.Match(clng(cell.offset(0,7)),rng1,0)
if iserror(res) then
cell.Entirerow.copy
Destination:=rng1.offset(rng1.rows.count,0).Resize (1,1)
end if
Next



--
Regards,
Tom Ogilvy