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:
Thanks in advance
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:
VBA Code:
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