Scooter172
06-12-2011, 07:04 PM
I have to change / remove some of the areas that are transfered using this code ( I inherited). I need to eliminate the transfer of [lrvLine4Start] and [lrvLine4Stop] Time information. Those range names have been eliminated from the page referenced. Any Ideas?
Sub sendWarrantDetail()
Application.ScreenUpdating = False
Workbooks.Open Filename:="H:\LRT\Ops Table Swap Folder\OpsTableSwapFile.xlsx", UpdateLinks:=3, writeresPassword:="6328"
'clear the swap file warrant
'Line 2
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2speed].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2track].ClearContents
'Line3
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3track].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3eic].ClearContents
'Line 4
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4stop].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4track].ClearContents
For c = 1 To 6
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4hand].Cells(c, 1) = "No"
Next c
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4mc].ClearContents
'Line 5
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5track].ClearContents
'Line 6
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].ClearContents
'move the data
'Line 1
'last priority: will require investigation into how best to have Excel know it is voided
Dim intI As Integer 'line number on active warrant
Dim intJ As Integer 'line number to use in swap file warrant
Dim nextLetter As Integer 'code for next letter to use on warrant line
'Line 2
intI = 1
intJ = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line2Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2letter].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2speed].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2speed].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2track].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 5 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(2, 1)
intJ = intJ + 1
End If
Do While intJ <= 5
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop
'Line 3
intI = 1
intJ = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line3Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3letter].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3track].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3eic].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3eic].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 5 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(3, 1)
intJ = intJ + 1
End If
Do While intJ <= 5
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop
'Line 4
intI = 1 'active warrant line index
intJ = 1 'swap warrant line index
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line4Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4letter].Cells(intI, 1)
'IF the Line 4 has stop &/or start times after midnight, THEN change the times accordingly, ELSE copy them as is
If Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 1) = "X" Or Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 1) = "x" Then
If Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 1) = "X" Or Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 1) = "x" Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = Left(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 3)
Else
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = 1
End If
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4stop].Cells(intJ, 1) = Left(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 3)
Else
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4stop].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1)
End If
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4track].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4hand].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4hand].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4mc].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4mc].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 6 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(4, 1)
intJ = intJ + 1
End If
Do While intJ <= 6
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop
'Line 5
intI = 1
intJ = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line5Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5letter].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5track].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 5 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(5, 1)
intJ = intJ + 1
End If
Do While intJ <= 5
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop
'Line 6
intI = 1
intJ = 1
Dim intK As Integer 'text index for swap warrant
Dim intL As Integer 'text index for active warrant
intK = 1
intL = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line6Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6letter].Cells(intL, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK + 1, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL + 1, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK + 2, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL + 2, 1)
intJ = intJ + 1
intK = intK + 3
End If
intI = intI + 1
intL = intL + 3
Next c
If intK <= 7 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(6, 1)
intK = intK + 3
End If
Do While intK <= 7 'as is this Do Loop will run at most once. Programmed this way for consistency with other lines and to allow easier expansion if more line 6's are added
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK - 3, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intL, 1) = 97
End If
intK = intK + 3
Loop
Workbooks("OpsTableSwapFile.xlsx").Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
Sub sendWarrantDetail()
Application.ScreenUpdating = False
Workbooks.Open Filename:="H:\LRT\Ops Table Swap Folder\OpsTableSwapFile.xlsx", UpdateLinks:=3, writeresPassword:="6328"
'clear the swap file warrant
'Line 2
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2speed].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2track].ClearContents
'Line3
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3track].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3eic].ClearContents
'Line 4
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4stop].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4track].ClearContents
For c = 1 To 6
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4hand].Cells(c, 1) = "No"
Next c
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4mc].ClearContents
'Line 5
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc1].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc2].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5track].ClearContents
'Line 6
' Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6inEffect].ClearContents
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].ClearContents
'move the data
'Line 1
'last priority: will require investigation into how best to have Excel know it is voided
Dim intI As Integer 'line number on active warrant
Dim intJ As Integer 'line number to use in swap file warrant
Dim nextLetter As Integer 'code for next letter to use on warrant line
'Line 2
intI = 1
intJ = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line2Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2letter].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2speed].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2speed].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line2track].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 5 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(2, 1)
intJ = intJ + 1
End If
Do While intJ <= 5
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine2letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop
'Line 3
intI = 1
intJ = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line3Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3letter].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3track].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3eic].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line3eic].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 5 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(3, 1)
intJ = intJ + 1
End If
Do While intJ <= 5
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine3letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop
'Line 4
intI = 1 'active warrant line index
intJ = 1 'swap warrant line index
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line4Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4letter].Cells(intI, 1)
'IF the Line 4 has stop &/or start times after midnight, THEN change the times accordingly, ELSE copy them as is
If Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 1) = "X" Or Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 1) = "x" Then
If Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 1) = "X" Or Right(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 1) = "x" Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = Left(ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1), 3)
Else
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = 1
End If
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4stop].Cells(intJ, 1) = Left(ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1), 3)
Else
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4start].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4start].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4stop].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4stop].Cells(intI, 1)
End If
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4track].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4hand].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4hand].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4mc].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line4mc].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 6 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(4, 1)
intJ = intJ + 1
End If
Do While intJ <= 6
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine4letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop
'Line 5
intI = 1
intJ = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line5Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5letter].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc1].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5loc1].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5loc2].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5loc2].Cells(intI, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5track].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line5track].Cells(intI, 1)
intJ = intJ + 1
End If
intI = intI + 1
Next c
If intJ <= 5 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(5, 1)
intJ = intJ + 1
End If
Do While intJ <= 5
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ - 1, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine5letter].Cells(intJ, 1) = 97
End If
intJ = intJ + 1
Loop
'Line 6
intI = 1
intJ = 1
Dim intK As Integer 'text index for swap warrant
Dim intL As Integer 'text index for active warrant
intK = 1
intL = 1
For Each c In ThisWorkbook.Sheets("LRV p1").[lrv1Line6Current]
If c = True Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6letter].Cells(intL, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK + 1, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL + 1, 1)
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6text].Cells(intK + 2, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1Line6text].Cells(intL + 2, 1)
intJ = intJ + 1
intK = intK + 3
End If
intI = intI + 1
intL = intL + 3
Next c
If intK <= 7 Then 'fill in the rest of the letters skipping "l" and going to "a" after "Z" starting with nextLetter and continuing in order
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = ThisWorkbook.Sheets("LRV p1").[lrv1nextLetter].Cells(6, 1)
intK = intK + 3
End If
Do While intK <= 7 'as is this Do Loop will run at most once. Programmed this way for consistency with other lines and to allow easier expansion if more line 6's are added
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK - 3, 1) + 1
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 108 Then
Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 109
Else
If Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intK, 1) = 123 Then Workbooks("OpsTableSwapFile.xlsx").Sheets("lrvWar1").[swapLine6letter].Cells(intL, 1) = 97
End If
intK = intK + 3
Loop
Workbooks("OpsTableSwapFile.xlsx").Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub