Sub MigrateJobListTable()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim file_path1 As String
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim My_Range1, My_Range2, My_Range3, My_Range4, My_Range5, My_Range6, My_Range7, My_Range8, My_Range9, My_Range10 As Range
Dim My_Range11, My_Range12, My_Range13, My_Range14, My_Range15, My_Range16, My_Range17, My_Range18, My_Range19, My_Range20 As Range
Dim My_Range21, My_Range22, My_Range23, My_Range24, My_Range25, My_Range26, My_Range27, My_Range28, My_Range29, My_Range30 As Range
Dim My_Range31, My_Range32, My_Range33, My_Range34, My_Range35, My_Range36, My_Range37, My_Range38, My_Range39, My_Range40 As Range
Dim My_Range41, My_Range42, My_Range43, My_Range44, My_Range45 As Range
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim LastRow3 As Long
Dim tb1 As ListObject
With Application
.EnableEvents = False
.ScreenUpdating = False
.CutCopyMode = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
Set wb1 = ThisWorkbook
file_path1 = "H:\Jobs\00 ENGINEERING DATA\Job List.xlsm" 'Path to Job List.xlsm
Set wb2 = Workbooks.Open(Filename:=file_path1, ReadOnly:=True, IgnoreReadOnlyRecommended:=True) 'Opens Job List.xlsm file in Read-Only mode
Set ws1 = wb1.Worksheets("Jobs")
Set ws2 = wb2.Worksheets("LogDetails")
Set ws3 = wb1.Worksheets("LogDetails")
Set ws4 = wb2.Worksheets("Jobs")
Set tbl = ws1.ListObjects("G2JobList") 'Destination table
LastRow1 = ws2.Range("A:A").SpecialCells(xlCellTypeLastCell).Row 'ws2 is the LogDetails sheet of the Source Workbook
LastRow2 = ws3.Range("A:A").SpecialCells(xlCellTypeLastCell).Row
Set My_Range42 = ws2.Range("A2", "F" & LastRow1)
Set My_Range43 = ws3.Range("A2", "F" & LastRow2)
Set My_Range44 = ws2.Range("G2", "G" & LastRow1)
Set My_Range45 = ws3.Range("G2", "G" & LastRow2)
LastRow3 = ws4.Range("G2JobList[#All]").SpecialCells(xlCellTypeLastCell).Row - 2
Set rng1 = ws1.Range("G2JobList[#All]").Resize(LastRow3, 164)
Set My_Range = Range("G2JobList[[#Data],[Job Name]]")
Set My_Range1 = ws4.Range("G2JobList[[#Data],[Job]:[Payment" & Chr(10) & "with" & Chr(10) & "Approval]]")
Set My_Range2 = ws4.Range("G2JobList[[#Data],[SITE Address]:[G1" & Chr(10) & "APPD Date]]")
Set My_Range3 = ws4.Range("G2JobList[[#Data],[G1 RLSD To" & Chr(10) & "ENGRG Date]:[G1 RLSD To" & Chr(10) & "PROD Date]]")
Set My_Range4 = ws4.Range("G2JobList[[#Data],[G1 CUST" & Chr(10) & "RQST Date]]")
Set My_Range5 = ws4.Range("G2JobList[[#Data],[G1" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Jack" & Chr(10) & "Vendor]]")
Set My_Range6 = ws4.Range("G2JobList[[#Data],[Jack" & Chr(10) & "PO]:[Jack" & Chr(10) & "REQD Date]]")
Set My_Range7 = ws4.Range("G2JobList[[#Data],[Jack" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Machine" & Chr(10) & "Vendor]]")
Set My_Range8 = ws4.Range("G2JobList[[#Data],[Machine" & Chr(10) & "PO]:[Machine" & Chr(10) & "REQD Date]]")
Set My_Range9 = ws4.Range("G2JobList[[#Data],[Machine" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Safety" & Chr(10) & "Vendor]]")
Set My_Range10 = ws4.Range("G2JobList[[#Data],[Safety" & Chr(10) & "PO]:[Safety" & Chr(10) & "REQD Date]]")
Set My_Range11 = ws4.Range("G2JobList[[#Data],[Safety" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Governor" & Chr(10) & "Vendor]]")
Set My_Range12 = ws4.Range("G2JobList[[#Data],[Governor" & Chr(10) & "PO]:[Governor" & Chr(10) & "REQD Date]]")
Set My_Range13 = ws4.Range("G2JobList[[#Data],[Governor" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Tail Sheave" & Chr(10) & "Vendor]]")
Set My_Range14 = ws4.Range("G2JobList[[#Data],[Tail Sheave" & Chr(10) & "PO]:[Tail Sheave" & Chr(10) & "REQD Date]]")
Set My_Range15 = ws4.Range("G2JobList[[#Data],[Tail Sheave" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Roller Guides" & Chr(10) & "Vendor]]")
Set My_Range16 = ws4.Range("G2JobList[[#Data],[Roller Guides" & Chr(10) & "PO]:[Roller Guides" & Chr(10) & "REQD Date]]")
Set My_Range17 = ws4.Range("G2JobList[[#Data],[Roller Guides" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[COMP Chain" & Chr(10) & "Vendor]]")
Set My_Range18 = ws4.Range("G2JobList[[#Data],[COMP Chain" & Chr(10) & "PO]:[COMP Chain" & Chr(10) & "REQD Date]]")
Set My_Range19 = ws4.Range("G2JobList[[#Data],[COMP Chain" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Ropes" & Chr(10) & "Vendor]]")
Set My_Range20 = ws4.Range("G2JobList[[#Data],[Ropes" & Chr(10) & "PO]:[Ropes" & Chr(10) & "REQD Date]]")
Set My_Range21 = ws4.Range("G2JobList[[#Data],[Ropes" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Oil Buffers" & Chr(10) & "Vendor]]")
Set My_Range22 = ws4.Range("G2JobList[[#Data],[Oil Buffers" & Chr(10) & "PO]:[Oil Buffers" & Chr(10) & "REQD Date]]")
Set My_Range23 = ws4.Range("G2JobList[[#Data],[Oil Buffers" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Rails" & Chr(10) & "Vendor]]")
Set My_Range24 = ws4.Range("G2JobList[[#Data],[Rails" & Chr(10) & "PO]:[Rails" & Chr(10) & "REQD Date]]")
Set My_Range25 = ws4.Range("G2JobList[[#Data],[Rails" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[CWT" & Chr(10) & "Vendor]]")
Set My_Range26 = ws4.Range("G2JobList[[#Data],[CWT" & Chr(10) & "PO]:[CWT" & Chr(10) & "REQD Date]]")
Set My_Range27 = ws4.Range("G2JobList[[#Data],[CWT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "Vendor]]")
Set My_Range28 = ws4.Range("G2JobList[[#Data],[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "PO]:[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "REQD Date]]")
Set My_Range29 = ws4.Range("G2JobList[[#Data],[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Cab" & Chr(10) & "Vendor]]")
Set My_Range30 = ws4.Range("G2JobList[[#Data],[Cab" & Chr(10) & "PO]:[Cab" & Chr(10) & "REQD Date]]")
Set My_Range31 = ws4.Range("G2JobList[[#Data],[Cab" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[ENT" & Chr(10) & "Vendor]]")
Set My_Range32 = ws4.Range("G2JobList[[#Data],[ENT" & Chr(10) & "PO]:[ENT" & Chr(10) & "REQD Date]]")
Set My_Range33 = ws4.Range("G2JobList[[#Data],[ENT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[FXTR" & Chr(10) & "Vendor]]")
Set My_Range34 = ws4.Range("G2JobList[[#Data],[FXTR" & Chr(10) & "PO]:[FXTR" & Chr(10) & "REQD Date]]")
Set My_Range35 = ws4.Range("G2JobList[[#Data],[FXTR" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[CONTR" & Chr(10) & "Vendor]]")
Set My_Range36 = ws4.Range("G2JobList[[#Data],[CONTR" & Chr(10) & "PO]:[CONTR" & Chr(10) & "REQD Date]]")
Set My_Range37 = ws4.Range("G2JobList[[#Data],[CONTR" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Door EQPT" & Chr(10) & "Vendor]]")
Set My_Range38 = ws4.Range("G2JobList[[#Data],[Door EQPT" & Chr(10) & "PO]:[Door EQPT" & Chr(10) & "REQD Date]]")
Set My_Range39 = ws4.Range("G2JobList[[#Data],[Door EQPT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Wiring" & Chr(10) & "Vendor]]")
Set My_Range40 = ws4.Range("G2JobList[[#Data],[Wiring" & Chr(10) & "PO]:[Wiring" & Chr(10) & "REQD Date]]")
Set My_Range41 = ws4.Range("G2JobList[[#Data],[Wiring" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Job Status" & Chr(10) & "Last Update]]")
tbl.Resize rng1
My_Range.Copy
ws1.Range("G2JobList[[#Data],[Job Name]]").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ws1.Range("G2JobList[[#Data],[Job]:[Payment" & Chr(10) & "with" & Chr(10) & "Approval]]").Value2 = My_Range1.Value2
ws1.Range("G2JobList[[#Data],[SITE Address]:[G1" & Chr(10) & "APPD Date]]").Value2 = My_Range2.Value2
ws1.Range("G2JobList[[#Data],[G1 RLSD To" & Chr(10) & "ENGRG Date]:[G1 RLSD To" & Chr(10) & "PROD Date]]").Value2 = My_Range3.Value2
ws1.Range("G2JobList[[#Data],[G1 CUST" & Chr(10) & "RQST Date]]").Value2 = My_Range4.Value2
ws1.Range("G2JobList[[#Data],[G1" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Jack" & Chr(10) & "Vendor]]").Value2 = My_Range5.Value2
ws1.Range("G2JobList[[#Data],[Jack" & Chr(10) & "PO]:[Jack" & Chr(10) & "REQD Date]]").Value2 = My_Range6.Value2
ws1.Range("G2JobList[[#Data],[Jack" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Machine" & Chr(10) & "Vendor]]").Value2 = My_Range7.Value2
ws1.Range("G2JobList[[#Data],[Machine" & Chr(10) & "PO]:[Machine" & Chr(10) & "REQD Date]]").Value2 = My_Range8.Value2
ws1.Range("G2JobList[[#Data],[Machine" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Safety" & Chr(10) & "Vendor]]").Value2 = My_Range9.Value2
ws1.Range("G2JobList[[#Data],[Safety" & Chr(10) & "PO]:[Safety" & Chr(10) & "REQD Date]]").Value2 = My_Range10.Value2
ws1.Range("G2JobList[[#Data],[Safety" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Governor" & Chr(10) & "Vendor]]").Value2 = My_Range11.Value2
ws1.Range("G2JobList[[#Data],[Governor" & Chr(10) & "PO]:[Governor" & Chr(10) & "REQD Date]]").Value2 = My_Range12.Value2
ws1.Range("G2JobList[[#Data],[Governor" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Tail Sheave" & Chr(10) & "Vendor]]").Value2 = My_Range13.Value2
ws1.Range("G2JobList[[#Data],[Tail Sheave" & Chr(10) & "PO]:[Tail Sheave" & Chr(10) & "REQD Date]]").Value2 = My_Range14.Value2
ws1.Range("G2JobList[[#Data],[Tail Sheave" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Roller Guides" & Chr(10) & "Vendor]]").Value2 = My_Range15.Value2
ws1.Range("G2JobList[[#Data],[Roller Guides" & Chr(10) & "PO]:[Roller Guides" & Chr(10) & "REQD Date]]").Value2 = My_Range16.Value2
ws1.Range("G2JobList[[#Data],[Roller Guides" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[COMP Chain" & Chr(10) & "Vendor]]").Value2 = My_Range17.Value2
ws1.Range("G2JobList[[#Data],[COMP Chain" & Chr(10) & "PO]:[COMP Chain" & Chr(10) & "REQD Date]]").Value2 = My_Range18.Value2
ws1.Range("G2JobList[[#Data],[COMP Chain" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Ropes" & Chr(10) & "Vendor]]").Value2 = My_Range19.Value2
ws1.Range("G2JobList[[#Data],[Ropes" & Chr(10) & "PO]:[Ropes" & Chr(10) & "REQD Date]]").Value2 = My_Range20.Value2
ws1.Range("G2JobList[[#Data],[Ropes" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Oil Buffers" & Chr(10) & "Vendor]]").Value2 = My_Range21.Value2
ws1.Range("G2JobList[[#Data],[Oil Buffers" & Chr(10) & "PO]:[Oil Buffers" & Chr(10) & "REQD Date]]").Value2 = My_Range22.Value2
ws1.Range("G2JobList[[#Data],[Oil Buffers" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Rails" & Chr(10) & "Vendor]]").Value2 = My_Range23.Value2
ws1.Range("G2JobList[[#Data],[Rails" & Chr(10) & "PO]:[Rails" & Chr(10) & "REQD Date]]").Value2 = My_Range24.Value2
ws1.Range("G2JobList[[#Data],[Rails" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[CWT" & Chr(10) & "Vendor]]").Value2 = My_Range25.Value2
ws1.Range("G2JobList[[#Data],[CWT" & Chr(10) & "PO]:[CWT" & Chr(10) & "REQD Date]]").Value2 = My_Range26.Value2
ws1.Range("G2JobList[[#Data],[CWT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "Vendor]]").Value2 = My_Range27.Value2
ws1.Range("G2JobList[[#Data],[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "PO]:[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "REQD Date]]").Value2 = My_Range28.Value2
ws1.Range("G2JobList[[#Data],[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Cab" & Chr(10) & "Vendor]]").Value2 = My_Range29.Value2
ws1.Range("G2JobList[[#Data],[Cab" & Chr(10) & "PO]:[Cab" & Chr(10) & "REQD Date]]").Value2 = My_Range30.Value2
ws1.Range("G2JobList[[#Data],[Cab" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[ENT" & Chr(10) & "Vendor]]").Value2 = My_Range31.Value2
ws1.Range("G2JobList[[#Data],[ENT" & Chr(10) & "PO]:[ENT" & Chr(10) & "REQD Date]]").Value2 = My_Range32.Value2
ws1.Range("G2JobList[[#Data],[ENT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[FXTR" & Chr(10) & "Vendor]]").Value2 = My_Range33.Value2
ws1.Range("G2JobList[[#Data],[FXTR" & Chr(10) & "PO]:[FXTR" & Chr(10) & "REQD Date]]").Value2 = My_Range34.Value2
ws1.Range("G2JobList[[#Data],[FXTR" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[CONTR" & Chr(10) & "Vendor]]").Value2 = My_Range35.Value2
ws1.Range("G2JobList[[#Data],[CONTR" & Chr(10) & "PO]:[CONTR" & Chr(10) & "REQD Date]]").Value2 = My_Range36.Value2
ws1.Range("G2JobList[[#Data],[CONTR" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Door EQPT" & Chr(10) & "Vendor]]").Value2 = My_Range37.Value2
ws1.Range("G2JobList[[#Data],[Door EQPT" & Chr(10) & "PO]:[Door EQPT" & Chr(10) & "REQD Date]]").Value2 = My_Range38.Value2
ws1.Range("G2JobList[[#Data],[Door EQPT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Wiring" & Chr(10) & "Vendor]]").Value2 = My_Range39.Value2
ws1.Range("G2JobList[[#Data],[Wiring" & Chr(10) & "PO]:[Wiring" & Chr(10) & "REQD Date]]").Value2 = My_Range40.Value2
ws1.Range("G2JobList[[#Data],[Wiring" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Job Status" & Chr(10) & "Last Update]]").Value2 = My_Range41.Value2
My_Range43.Clear
My_Range43.Value = My_Range42.Value
My_Range45.Clear
My_Range45.Formula = My_Range44.Formula
ws3.Activate
ws3.Range("A2").Select
ActiveWindow.ScrollRow = ActiveCell.Row
ws1.Activate
ws1.Range("A3").Select
ActiveWindow.ScrollRow = ActiveCell.Row
wb2.Close SaveChanges:=False 'Un-Remark this line of code after all testing is complete, SPS, 02/07/24
With Application
.EnableEvents = True
.ScreenUpdating = True
.CutCopyMode = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With
End Sub