Adjust current macro

[GLK]

Hi all,

I hope someone can help me.
4 times a day my collegues download an excel sheet from a website.
The sheet has about 48 columns and they need only 7 of them.
I've already got a macro which deletes rows and columns etc etc.
But I'm looking for an addition..

The containers on that list are compared with another sheet which contains certain ISO codes.
The current macro deletes containers from the sheet if the ISO code is in the other sheet.

Actually I want the macro to delete the containers from sheet1 and copy the deleted rows to sheet2,
Or copy them to the end of the sheet and colour the rows yellow.

I hope someone can help me adjust the macro.

Current macro is:

Sub Scanlijst()

Dim ar As Variant, iso As Variant

Dim dict As Object, i As Long



ar = Sheets(1).Cells(5, 1).CurrentRegion

Set iso = GetObject("G:\APMTR Data-EDI Center\Data-EDI\Documentation\ISO Codes Open Top en Flat containers\ISO codes (Open top- en flat containers).xls").Sheets(1).Cells(1, 1).CurrentRegion

Set dict = CreateObject("scripting.dictionary")

Application.ScreenUpdating = False



For i = 1 To UBound(ar)

If Application.CountIf(iso.Columns(1), ar(i, 3)) = 0 And ar(i, 31) <> "" And ar(i, 31) <> "-" Or ar(i, 7) = "Arrived" Then

dict(ar(i, 2)) = Array(ar(i, 2), ar(i, 3), ar(i, 5), ar(i, 6), ar(i, 7), ar(i, 31), ar(i, 40))

End If

Next



With Sheets(1)

.UsedRange.Clear

.Cells(1, 1).Resize(dict.Count, 7) = Application.Index(dict.items, 0, 0)

.Cells(1, 1).CurrentRegion.AutoFilter

.Cells(1, 1).CurrentRegion.Columns.AutoFit

.Cells(1, 1).CurrentRegion.HorizontalAlignment = xlLeft

Range("A2").Select

ActiveWindow.FreezePanes = True



End With



iso.Parent.Parent.Close 0

 End Sub

Thanks in advance