vba_clcheck

Sub locaC()

'fuzhibiaoge

Dim MyBook1 As Workbook
Set MyBook1 = ActiveWorkbook

Workbooks.Open ("C:\Users\GSC.BFU\Desktop\bOB\Clarence Location Check\Clarence Location.xlsx")
Set MyBook3 = ActiveWorkbook
MyBook3.Sheets(1).Copy MyBook1.Sheets("Page1_1")
MyBook3.Close
MyBook1.Sheets("sheet1").Name = "Clarence Location"

Workbooks.Open ("C:\Users\GSC.BFU\Desktop\bOB\Clarence Location Check\Off Dock & Duty Paids Profile.xlsx")
Set MyBook4 = ActiveWorkbook
MyBook4.Sheets(1).Copy MyBook1.Sheets("Page1_1")
MyBook4.Close
MyBook1.Sheets("sheet1").Name = "Off Dock List"

Workbooks.Open ("C:\Users\GSC.BFU\Desktop\bOB\Clarence Location Check\WK29-Off Dock-0TP6GW1PL.xlsx")
Set MyBook5 = ActiveWorkbook
MyBook5.Sheets(1).Copy MyBook1.Sheets("Page1_1")
MyBook5.Close


'Off dock

'IF BAOHAN ZIFU AND CL HOUSIWEI DAYU4
'THEN TIQU CONTAINER BL CUSTOMER CL
'OFF DCOK GAI CHUAN & RIQI & TIAN RU TIQU DE CONTAINER BL CUSTOMER CL


For x7 = 4 To Sheets("Page1_1").Range("a65536").End(xlUp).ROW

If (Sheets("Page1_1").Cells(x7, "n") Like "*Canadian Tire*" Or Sheets("Page1_1").Cells(x7, "n") Like "*CANADIAN TIRE*") _
And _
(Sheets("Page1_1").Cells(x7, "k") Like "*5525*" Or Sheets("Page1_1").Cells(x7, "k") Like "*5491*" _
Or Sheets("Page1_1").Cells(x7, "k") Like "*4222*" Or Sheets("Page1_1").Cells(x7, "k") Like "*4945*") Then

Sheets("Off Dock").Cells(n + 10, "a") = Sheets("Page1_1").Cells(x7, "l")
Sheets("Off Dock").Cells(n + 10, "b") = Sheets("Page1_1").Cells(x7, "d")
Sheets("Off Dock").Cells(n + 10, "c") = Sheets("Page1_1").Cells(x7, "n")
Sheets("Off Dock").Cells(n + 10, "d") = Sheets("Page1_1").Cells(x7, "k")

n = n + 1

End If

If (Sheets("Page1_1").Cells(x7, "n") Like "*FGL Sport*" Or Sheets("Page1_1").Cells(x7, "n") Like "*FGL SPORT*" _
Or Sheets("Page1_1").Cells(x7, "n") Like "*Marks Work*" Or Sheets("Page1_1").Cells(x7, "n") Like "*MARKS WORK*" _
Or Sheets("Page1_1").Cells(x7, "n") Like "*Target*" Or Sheets("Page1_1").Cells(x7, "n") Like "*TARGET*" _
Or Sheets("Page1_1").Cells(x7, "n") Like "*Toys R Us*" Or Sheets("Page1_1").Cells(x7, "n") Like "*TOUS R US*" _
Or Sheets("Page1_1").Cells(x7, "n") Like "*Yamaha*" Or Sheets("Page1_1").Cells(x7, "n") Like "*YAMAHA*") Then

Sheets("Off Dock").Cells(n + 10, "a") = Sheets("Page1_1").Cells(x7, "l")
Sheets("Off Dock").Cells(n + 10, "b") = Sheets("Page1_1").Cells(x7, "d")
Sheets("Off Dock").Cells(n + 10, "c") = Sheets("Page1_1").Cells(x7, "n")
Sheets("Off Dock").Cells(n + 10, "d") = Sheets("Page1_1").Cells(x7, "k")
Sheets("Off Dock").Cells(n + 10, "d").Interior.Color = 255

n = n + 1

End If

Next x7

Sheets("Off Dock").[b4] = Sheets("Page1_1").[e4]
Sheets("Off Dock").[d3] = Date


'Schenker

Sheets.Add
Sheets("Sheet4").Name = "Schenker"

Dim ROW As Integer
Dim SKER()

ROW = Sheets("Page1_1").Range("b65536").End(xlUp).ROW

ReDim SKER(1 To ROW, 1 To 5)

For X1 = 4 To ROW

If Sheets("Page1_1").Cells(X1, "N") Like "*SCHENKER*" Or Sheets("Page1_1").Cells(X1, "N") Like "*Schenker*" Then

SKER(X1, 1) = Sheets("Page1_1").Cells(X1, "D")
SKER(X1, 2) = Sheets("Page1_1").Cells(X1, "L")
SKER(X1, 3) = Sheets("Page1_1").Cells(X1, "J")
SKER(X1, 4) = Sheets("Page1_1").Cells(X1, "N")

End If

If Sheets("Page1_1").Cells(X1, "I") <> "" Then

SKER(X1, 5) = Sheets("Page1_1").Cells(X1, "I") & Sheets("Page1_1").Cells(X1, "J") & Sheets("Page1_1").Cells(X1, "K")

ElseIf Sheets("Page1_1").Cells(X1, "I") = "" Then

SKER(X1, 5) = Sheets("Page1_1").Cells(X1, "H") & Sheets("Page1_1").Cells(X1, "J") & Sheets("Page1_1").Cells(X1, "K")

End If

Next X1

For X2 = 4 To ROW

If SKER(X2, 1) <> "" Then

Sheets("Schenker").Cells(k + 2, "A") = SKER(X2, 1)
Sheets("Schenker").Cells(k + 2, "B") = SKER(X2, 2)
Sheets("Schenker").Cells(k + 2, "C") = SKER(X2, 3)
Sheets("Schenker").Cells(k + 2, "D") = SKER(X2, 4)

k = k + 1

End If

Next X2

Sheets("Schenker").[a1] = "BKH - Booking Ref"
Sheets("Schenker").[b1] = "EQ - Container Number"
Sheets("Schenker").[c1] = "BKH - Customs Clearance"
Sheets("Schenker").[d1] = "PARC - Full Name (Consignee) (Manual)"

    Sheets("Schenker").Cells.EntireColumn.AutoFit
    


'Clarence Location cEHCKE

Sheets.Add

Sheets("Sheet5").Name = "Clarence Location Error BL"

For X3 = 2 To Sheets("Clarence Location").Range("b65536").End(xlUp).ROW

Sheets("Clarence Location").Cells(X3, "D") = Sheets("Clarence Location").Cells(X3, "A") & Sheets("Clarence Location").Cells(X3, "B") & Sheets("Clarence Location").Cells(X3, "C")

Next X3


For X4 = 1 To ROW

If IsError(Application.VLookup(SKER(X4, 5), Sheets("Clarence Location").Range("D:D"), 1, False)) Then

Sheets("Clarence Location Error BL").Cells(m + 1, "A") = Sheets("Page1_1").Cells(X4, "D")

m = m + 1

End If

Next X4

Columns("A:A").RemoveDuplicates Columns:=1, Header:=xlNo

On Error Resume Next

For X5 = 1 To Sheets("Clarence Location Error BL").Range("A65536").End(xlUp).ROW

Cells(X5, "B") = Sheets("Page1_1").Range("D:D").Find(Cells(X5, "A")).Offset(0, 3)
Cells(X5, "C") = Sheets("Page1_1").Range("D:D").Find(Cells(X5, "A")).Offset(0, 4)
Cells(X5, "D") = Sheets("Page1_1").Range("D:D").Find(Cells(X5, "A")).Offset(0, 5)
Cells(X5, "E") = Sheets("Page1_1").Range("D:D").Find(Cells(X5, "A")).Offset(0, 6)
Cells(X5, "F") = Sheets("Page1_1").Range("D:D").Find(Cells(X5, "A")).Offset(0, 7)

Next X5

On Error GoTo 0


   Sheets("Clarence Location Error BL").Cells.EntireColumn.AutoFit
   
   'Off dock



MsgBox "Done"



End Sub