vba_hold_report

Sub Hold()

Dim MyBook1 As Workbook
Set MyBook1 = ActiveWorkbook


Workbooks.Open ("\\Cngscapl-spf303\Customer_Care\Doc_Import\Share\AM Team\01 IB - LARA\15 Personal Folder\bOB\Database_Bob.accdb")
Set MyBook2 = ActiveWorkbook
MyBook2.Sheets(2).Copy MyBook1.Sheets("Page1_1")

MyBook1.Sheets("Sheet2").Name = "Holds History"



For z = 4 To Sheets("Page1_1").Range("a65536").End(xlUp).ROW
If Sheets("Page1_1").Cells(z, "o") Like "*day*" Then

Sheets("Page1_1").Cells(z, "q") = Split(Sheets("Page1_1").Cells(z, "o"), " day")

Else

Sheets("Page1_1").Cells(z, "q") = 1

End If
Next z


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

If Sheets("Page1_1").Cells(x, "L") >= 1 And Sheets("Page1_1").Cells(x, "M") = 0 And Sheets("Page1_1").Cells(x, "q") < 5 And "US" = Left(Sheets("Page1_1").Cells(x, "G"), 2) Then

Sheets("Page1_1").Cells(x, "p") = Sheets("Page1_1").Cells(x, "e") & "," & "DA,"

ElseIf Sheets("Page1_1").Cells(x, "L") = 0 And Sheets("Page1_1").Cells(x, "M") >= 1 And Sheets("Page1_1").Cells(x, "q") < 5 And "US" = Left(Sheets("Page1_1").Cells(x, "G"), 2) Then

Sheets("Page1_1").Cells(x, "p") = Sheets("Page1_1").Cells(x, "e") & "," & ",CS"

ElseIf Sheets("Page1_1").Cells(x, "L") >= 1 And Sheets("Page1_1").Cells(x, "M") >= 1 And Sheets("Page1_1").Cells(x, "q") < 5 And "US" = Left(Sheets("Page1_1").Cells(x, "G"), 2) Then

Sheets("Page1_1").Cells(x, "p") = Sheets("Page1_1").Cells(x, "e") & "," & "DA,CS"

End If

Next x

Sheets.Add
ActiveSheet.Name = "Holds BLs"

k = 2


For Y = 4 To Sheets("Page1_1").Range("P65536").End(xlUp).ROW

If IsError(Application.VLookup(Sheets("Page1_1").Cells(Y, "p"), Sheets("Holds History").Range("A:A"), 1, False)) And Sheets("Page1_1").Cells(Y, "p") <> "" Then

Sheets("Holds BLs").Cells(k, "a") = Sheets("Page1_1").Cells(Y, "p")

k = k + 1

End If


Next Y

For X1 = 1 To Sheets("Holds BLs").Range("a65536").End(xlUp).ROW
Sheets("Holds BLs").Cells(X1, "B") = Split(Sheets("Holds BLs").Cells(X1, "a"), ",")
If Sheets("Holds BLs").Cells(X1, "A") Like "*DA*" Then
Sheets("Holds BLs").Cells(X1, "C") = "DA"
End If
If Sheets("Holds BLs").Cells(X1, "A") Like "*CS*" Then
Sheets("Holds BLs").Cells(X1, "D") = "CS"
End If
Next X1

[b1] = "BL Number"
[c1] = "Usda Hold"
[d1] = "Uscs Hold"
Cells.EntireColumn.AutoFit

ROW1 = MyBook2.Sheets("Sheet2").Range("a65536").End(xlUp).ROW

MyBook2.Sheets("Sheet2").Cells(ROW1 + 1, "A") = Date
MyBook2.Sheets("Sheet2").Cells(ROW1 + 1, "A").Interior.Color = 5296274

For X2 = 2 To MyBook1.Sheets("Holds BLs").Range("a65536").End(xlUp).ROW

MyBook2.Sheets("Sheet2").Cells(ROW1 + 2, "A") = MyBook1.Sheets("Holds BLs").Cells(X2, "A")

ROW1 = ROW1 + 1

Next X2


MyBook2.Save
MyBook2.Close

MsgBox "Done"

End Sub