Sub PopulateOrderByReportWS_WKG1()
Application.ScreenUpdating = True
Application.EnableEvents = False
Application.DisplayAlerts = True
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim tb1 As ListObject
Dim tb2 As ListObject
Dim lc1, lc2, lc3, lc4, lc5, lc6, lc7, lc8, lc9, lc10, lc11, lc12, lc13, lc14, lc15, lc16, lc17, lc18 As ListColumn
Dim lc19, lc20, lc21, lc22, lc23, lc24, lc25, lc26, lc27, lc28, lc29, lc30, lc31, lc32, lc33, lc34, lc99 As ListColumn
Dim rng1 As Range
Dim i As Long
Dim lRw As Long
Set ws1 = Sheets("Jobs")
Set ws2 = Sheets("Order By Report")
Set tb1 = ws1.ListObjects("G2JobList")
Set tb2 = ws2.ListObjects("Order_By_Report")
Set lc1 = ws1.ListObjects("G2JobList").ListColumns("Jack" & Chr(10) & "PO")
Set lc2 = ws1.ListObjects("G2JobList").ListColumns("Jack" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc3 = ws1.ListObjects("G2JobList").ListColumns("Machine" & Chr(10) & "PO")
Set lc4 = ws1.ListObjects("G2JobList").ListColumns("Machine" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc5 = ws1.ListObjects("G2JobList").ListColumns("Safety" & Chr(10) & "PO")
Set lc6 = ws1.ListObjects("G2JobList").ListColumns("Safety" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc7 = ws1.ListObjects("G2JobList").ListColumns("Governor" & Chr(10) & "PO")
Set lc8 = ws1.ListObjects("G2JobList").ListColumns("Governor" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc9 = ws1.ListObjects("G2JobList").ListColumns("Tail Sheave" & Chr(10) & "PO")
Set lc10 = ws1.ListObjects("G2JobList").ListColumns("Tail Sheave" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc11 = ws1.ListObjects("G2JobList").ListColumns("Roller Guides" & Chr(10) & "PO")
Set lc12 = ws1.ListObjects("G2JobList").ListColumns("Roller Guides" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc13 = ws1.ListObjects("G2JobList").ListColumns("COMP Chain" & Chr(10) & "PO")
Set lc14 = ws1.ListObjects("G2JobList").ListColumns("COMP Chain" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc15 = ws1.ListObjects("G2JobList").ListColumns("Ropes" & Chr(10) & "PO")
Set lc16 = ws1.ListObjects("G2JobList").ListColumns("Ropes" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc17 = ws1.ListObjects("G2JobList").ListColumns("Oil Buffers" & Chr(10) & "PO")
Set lc18 = ws1.ListObjects("G2JobList").ListColumns("Oil Buffers" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc19 = ws1.ListObjects("G2JobList").ListColumns("Rails" & Chr(10) & "PO")
Set lc20 = ws1.ListObjects("G2JobList").ListColumns("Rails" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc21 = ws1.ListObjects("G2JobList").ListColumns("CWT" & Chr(10) & "PO")
Set lc22 = ws1.ListObjects("G2JobList").ListColumns("CWT" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc23 = ws1.ListObjects("G2JobList").ListColumns("Cab" & Chr(10) & "PO")
Set lc24 = ws1.ListObjects("G2JobList").ListColumns("Cab" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc25 = ws1.ListObjects("G2JobList").ListColumns("ENT" & Chr(10) & "PO")
Set lc26 = ws1.ListObjects("G2JobList").ListColumns("ENT" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc27 = ws1.ListObjects("G2JobList").ListColumns("FXTR" & Chr(10) & "PO")
Set lc28 = ws1.ListObjects("G2JobList").ListColumns("FXTR" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc29 = ws1.ListObjects("G2JobList").ListColumns("CONTR" & Chr(10) & "PO")
Set lc30 = ws1.ListObjects("G2JobList").ListColumns("CONTR" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc31 = ws1.ListObjects("G2JobList").ListColumns("Door EQPT" & Chr(10) & "PO")
Set lc32 = ws1.ListObjects("G2JobList").ListColumns("Door EQPT" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc33 = ws1.ListObjects("G2JobList").ListColumns("Wiring" & Chr(10) & "PO")
Set lc34 = ws1.ListObjects("G2JobList").ListColumns("Wiring" & Chr(10) & "Order By" & Chr(10) & "Date")
Set lc99 = ws1.ListObjects("G2JobList").ListColumns("G1" & Chr(10) & "Job #")
col1 = lc1.Range.Column
col2 = lc2.Range.Column
col3 = lc3.Range.Column
col4 = lc4.Range.Column
col5 = lc5.Range.Column
col6 = lc6.Range.Column
col7 = lc7.Range.Column
col8 = lc8.Range.Column
col9 = lc9.Range.Column
col10 = lc10.Range.Column
col11 = lc11.Range.Column
col12 = lc12.Range.Column
col13 = lc13.Range.Column
col14 = lc14.Range.Column
col15 = lc15.Range.Column
col16 = lc16.Range.Column
col17 = lc17.Range.Column
col18 = lc18.Range.Column
col19 = lc19.Range.Column
col20 = lc20.Range.Column
col21 = lc21.Range.Column
col22 = lc22.Range.Column
col23 = lc23.Range.Column
col24 = lc24.Range.Column
col25 = lc25.Range.Column
col26 = lc26.Range.Column
col27 = lc27.Range.Column
col28 = lc28.Range.Column
col29 = lc29.Range.Column
col30 = lc30.Range.Column
col31 = lc31.Range.Column
col32 = lc32.Range.Column
col33 = lc33.Range.Column
col34 = lc34.Range.Column
col99 = lc99.Range.Column
On Error Resume Next
tb2.DataBodyRange.Delete
ws1.Activate
ActiveSheet.ListObjects("G2JobList").Range.AutoFilter
ActiveSheet.ListObjects("G2JobList").Range.AutoFilter Field:=col99, Criteria1:="<>"
ActiveSheet.ListObjects("G2JobList").Range.AutoFilter Field:=col5, Criteria1:="="
ActiveSheet.ListObjects("G2JobList").Range.AutoFilter Field:=col6, Criteria1:=">=" & Date, Operator:= _
xlAnd, Criteria2:="<=" & Date + 28
With tb1
If Range("G2JobList[[#All],[Job Name]]").SpecialCells(xlCellTypeVisible).Count > 1 Then
Range("G2JobList[[Job Name]]").SpecialCells(xlCellTypeVisible).Copy
With Range("Order_By_Report[[Job Name]]")
n = Columns(.Column).Resize(, .Columns.Count).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ws2.Cells(n + 1, .Column).PasteSpecial xlPasteValues
End With
End If
If Range("G2JobList[[#All],[Job Name]]").SpecialCells(xlCellTypeVisible).Count > 1 Then
Range("G2JobList[[G1" & Chr(10) & "Job '#]]").SpecialCells(xlCellTypeVisible).Copy
With Range("Order_By_Report[[Job '#]]")
n = Columns(.Column).Resize(, .Columns.Count).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ws2.Cells(n + 1, .Column).PasteSpecial xlPasteValues
End With
End If
If Range("G2JobList[[#All],[Job Name]]").SpecialCells(xlCellTypeVisible).Count > 1 Then
Range("G2JobList[[Safety" & Chr(10) & "Vendor]], G2JobList[[Safety" & Chr(10) & "Order By" & Chr(10) & "Date]]").SpecialCells(xlCellTypeVisible).Copy
With Range("Order_By_Report[[Vendor]]")
n = Columns(.Column).Resize(, .Columns.Count).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ws2.Cells(n + 1, .Column).PasteSpecial xlPasteValues
End With
End If
With tb2.Range.Borders()
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End With
If Range("G2JobList[[#All],[Job Name]]").SpecialCells(xlCellTypeVisible).Count > 1 Then
For Each rng1 In ws2.Range("Order_By_Report[[Equipment]]")
If rng1 = vbNullString Then rng1 = "Safety"
lRw = Cells(Rows.Count, 1).End(xlUp).Row
Next
End If
ws1.Activate
ActiveSheet.ListObjects("G2JobList").Range.AutoFilter
ActiveSheet.ListObjects("G2JobList").Range.AutoFilter Field:=col99, Criteria1:="<>"
ActiveSheet.ListObjects("G2JobList").Range.AutoFilter Field:=col7, Criteria1:="="
ActiveSheet.ListObjects("G2JobList").Range.AutoFilter Field:=col8, Criteria1:=">=" & Date, Operator:= _
xlAnd, Criteria2:="<=" & Date + 28
With tb1
If Range("G2JobList[[#All],[Job Name]]").SpecialCells(xlCellTypeVisible).Count > 1 Then
Range("G2JobList[[Job Name]]").SpecialCells(xlCellTypeVisible).Copy
With Range("Order_By_Report[[Job Name]]")
n = Columns(.Column).Resize(, .Columns.Count).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ws2.Cells(n + 1, .Column).PasteSpecial xlPasteValues
End With
End If
If Range("G2JobList[[#All],[Job Name]]").SpecialCells(xlCellTypeVisible).Count > 1 Then
Range("G2JobList[[G1" & Chr(10) & "Job '#]]").SpecialCells(xlCellTypeVisible).Copy
With Range("Order_By_Report[[Job '#]]")
n = Columns(.Column).Resize(, .Columns.Count).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ws2.Cells(n + 1, .Column).PasteSpecial xlPasteValues
End With
End If
If Range("G2JobList[[#All],[Job Name]]").SpecialCells(xlCellTypeVisible).Count > 1 Then
Range("G2JobList[[Governor" & Chr(10) & "Vendor]], G2JobList[[Governor" & Chr(10) & "Order By" & Chr(10) & "Date]]").SpecialCells(xlCellTypeVisible).Copy
With Range("Order_By_Report[[Vendor]]")
n = Columns(.Column).Resize(, .Columns.Count).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ws2.Cells(n + 1, .Column).PasteSpecial xlPasteValues
End With
End If
With tb2.Range.Borders()
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End With
If Range("G2JobList[[#All],[Job Name]]").SpecialCells(xlCellTypeVisible).Count > 1 Then
For Each rng1 In ws2.Range("Order_By_Report[[Equipment]]")
If rng1 = vbNullString Then rng1 = "Governor"
lRw = Cells(Rows.Count, 1).End(xlUp).Row
Next
End If
ws1.Activate
ActiveSheet.ListObjects("G2JobList").Range.AutoFilter
ActiveSheet.ListObjects("G2JobList").Range.AutoFilter
Range("A1").Select
Set cell = ActiveCell
ActiveWindow.ScrollRow = cell.Row
Range("A3").Select
ws2.Activate
Range("B1").Select
Set cell = ActiveCell
ActiveWindow.ScrollRow = cell.Row
Range("B3").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub