Here it is. It is a bit long.
Dim Saskour20per As String, saskour40per As String, saskourper As String,
sask53 As Double, winn53 As Double, calg53 As Double, edmt53 As Double, van53
As Double, ccsi As String, PU As Double
Sub LaneSpecificReport()
Rows("1:1").Delete
Range("E:H,J:AA").Delete
Cells.EntireColumn.AutoFit
Columns("A:A").Select
Selection.Cut
Columns("F:F").Select
Selection.Insert
Columns("B:C").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert
'Deleting not needed columns
Cells.Replace What:="45", Replacement:="40"
Cells.Replace What:="48", Replacement:="40"
'Replacing 45' and 48' containers for 40'
Range("F1").Value = "=A1&""""&B1&""""&C1"
Range("F1").AutoFill Destination:=Range("F1:F1100")
Range("F1").EntireColumn.AutoFit
'joining columns to create a criteria by lane to count number of
containers per customer
Columns("B:B").Insert
Range("B1").Value = "=COUNTIF(g:g,g1)"
Range("B1").AutoFill Destination:=Range("B1:B1100")
'counting number of containers per customer
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("G1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
'sorting by destination and customer to delete dupplicate rows
Sheets.Add
Sheets("Sheet1").Select
Selection.Copy
Sheets("Sheet2").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues
'copying values to other sheet to delete dupplicate rows
Range("G1").Select
For Customer = 1 To 1100
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next Customer
'deleting duplicate rows
Range("A1").EntireRow.Insert
Cells.Select
Selection.Sort Key1:=Range("A1"), OrderCustom:=6
'sorting by our order of destinations
Range("H1").Value = "=C1&""""&F1"
Range("H1").AutoFill Destination:=Range("H1:H100")
'joining columns to count total of CN, CP and our supply
Range("A1").Select
For Lane = 1 To 100
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
End If
Next Lane
'inserting rows for the summary
Range("A1").EntireRow.Insert
Range("B2").Select
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "C"), Cells(EndRow, "C"))
sask20 = Application.SumIf(CriteriaRange, "=20", SumRange)
sask40 = Application.SumIf(CriteriaRange, "=40", SumRange)
sask53 = InputBox("How many NFFU were sent out to Saskatoon?", "NFFU's
to Saskatoon", "0")
SaskTotal = (sask20 + sask40 + sask53)
'calculating total of 20's, 40's and 53's for Sask
Range("B2").Select
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "H"), Cells(EndRow, "H"))
saskcn20 = Application.SumIf(CriteriaRange, "=20CNF001", SumRange)
saskcn40 = Application.SumIf(CriteriaRange, "=40CNF001", SumRange)
saskcp20 = Application.SumIf(CriteriaRange, "=20CPF001", SumRange)
saskcp40 = Application.SumIf(CriteriaRange, "=40CPF001", SumRange)
saskour20 = (sask20 - saskcn20 - saskcp20)
saskour40 = (sask40 - saskcn40 - saskcp40)
If sask20 <> 0 Then
Saskour20per = (saskour20 / sask20)
Saskour20per = Format(Saskour20per, "0%")
Else
Saskour20per = "0%"
End If
saskour40per = (saskour40 / sask40)
saskour40per = Format(saskour40per, "0%")
saskourper = (saskour20 + saskour40 + sask53) / SaskTotal
saskourper = Format(saskourper, "0%")
'Calculating total of CP, CN and our supply
Range("A1").Select
Selection.Value = "Saskatoon " & sask20 & " - 20's & " & sask40 & " -
40's & " & sask53 & " - NFFU's"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
If saskcn20 = 0 And saskcn40 = 0 And saskcp20 = 0 And saskcp40 = 0 Then
Selection.Value = "100% of the equipment supplied by us"
Else
Selection.Value = Saskour20per & " of the 20' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = saskour40per & " of the 40' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = "We supplied " & saskourper & " of the equipment sent
into Saskatoon"
End If
ActiveCell.Offset(1, 0).Select
'Recording the summary
Do
If ActiveCell.Offset(1, 0) <> "" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset <> ""
'looking for next active cell to select new range (new destination)
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "C"), Cells(EndRow, "C"))
winn20 = Application.SumIf(CriteriaRange, "=20", SumRange)
winn40 = Application.SumIf(CriteriaRange, "=40", SumRange)
winn53 = InputBox("How many NFFU were sent out to Winnipeg?", "NFFU's to
Winnipeg", "0")
winnTotal = (winn20 + winn40 + winn53)
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "H"), Cells(EndRow, "H"))
winncn20 = Application.SumIf(CriteriaRange, "=20CNF001", SumRange)
winncn40 = Application.SumIf(CriteriaRange, "=40CNF001", SumRange)
winncp20 = Application.SumIf(CriteriaRange, "=20CPF001", SumRange)
winncp40 = Application.SumIf(CriteriaRange, "=40CPF001", SumRange)
winnour20 = (winn20 - winncn20 - winncp20)
winnour40 = (winn40 - winncn40 - winncp40)
If winn20 <> 0 Then
winnour20per = (winnour20 / winn20)
winnour20per = Format(winnour20per, "0%")
Else
winnour20per = "0%"
End If
winnour40per = (winnour40 / winn40)
winnour40per = Format(winnour40per, "0%")
winnourper = (winnour20 + winnour40 + winn53) / winnTotal
winnourper = Format(winnourper, "0%")
ActiveCell.Offset(-1, 0).Select
Selection.Value = " Winnipeg " & winn20 & " - 20's & " & winn40 & " -
40's & " & winn53 & " - NFFU's"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
If winncn20 = 0 And winncn40 = 0 And winncp20 = 0 And winncp40 = 0 Then
Selection.Value = "100% of the equipment supplied by us"
Else
Selection.Value = winnour20per & " of the 20' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = winnour40per & " of the 40' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = "We supplied " & winnourper & " of the equipment sent
into Winnipeg"
End If
ActiveCell.Offset(1, 0).Select
Do
If ActiveCell.Offset(1, 0) <> "" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset <> ""
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "C"), Cells(EndRow, "C"))
calg20 = Application.SumIf(CriteriaRange, "=20", SumRange)
calg40 = Application.SumIf(CriteriaRange, "=40", SumRange)
calg53 = InputBox("How many NFFU were sent out to Calgary?", "NFFU's to
Calgary", "0")
calgTotal = (calg20 + calg40 + calg53)
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "H"), Cells(EndRow, "H"))
calgcn20 = Application.SumIf(CriteriaRange, "=20CNF001", SumRange)
calgcn40 = Application.SumIf(CriteriaRange, "=40CNF001", SumRange)
calgcp20 = Application.SumIf(CriteriaRange, "=20CPF001", SumRange)
calgcp40 = Application.SumIf(CriteriaRange, "=40CPF001", SumRange)
calgour20 = (calg20 - calgcn20 - calgcp20)
calgour40 = (calg40 - calgcn40 - calgcp40)
If calg20 <> 0 Then
calgour20per = (calgour20 / calg20)
calgour20per = Format(calgour20per, "0%")
Else
calgour20per = "0%"
End If
calgour40per = (calgour40 / calg40)
calgour40per = Format(calgour40per, "0%")
calgourper = (calgour20 + calgour40 + calg53) / calgTotal
calgourper = Format(calgourper, "0%")
ActiveCell.Offset(-1, 0).Select
Selection.Value = " Calgary " & calg20 & " - 20's & " & calg40 & " -
40's & " & calg53 & " - NFFU's"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
If calgcn20 = 0 And calgcn40 = 0 And calgcp20 = 0 And calgcp40 = 0 Then
Selection.Value = "100% of the equipment supplied by us"
Else
Selection.Value = calgour20per & " of the 20' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = calgour40per & " of the 40' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = "We supplied " & calgourper & " of the equipment sent
into Calgary"
End If
ActiveCell.Offset(1, 0).Select
Do
If ActiveCell.Offset(1, 0) <> "" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset <> ""
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "C"), Cells(EndRow, "C"))
edmt20 = Application.SumIf(CriteriaRange, "=20", SumRange)
edmt40 = Application.SumIf(CriteriaRange, "=40", SumRange)
edmt53 = InputBox("How many NFFU were sent out to Edmonton?", "NFFU's to
Edmonton", "0")
edmtTotal = (edmt20 + edmt40 + edmt53)
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "H"), Cells(EndRow, "H"))
edmtcn20 = Application.SumIf(CriteriaRange, "=20CNF001", SumRange)
edmtcn40 = Application.SumIf(CriteriaRange, "=40CNF001", SumRange)
edmtcp20 = Application.SumIf(CriteriaRange, "=20CPF001", SumRange)
edmtcp40 = Application.SumIf(CriteriaRange, "=40CPF001", SumRange)
edmtour20 = (edmt20 - edmtcn20 - edmtcp20)
edmtour40 = (edmt40 - edmtcn40 - edmtcp40)
If edmt20 <> 0 Then
edmtour20per = (edmtour20 / edmt20)
edmtour20per = Format(edmtour20per, "0%")
Else
edmtour20per = "0%"
End If
edmtour40per = (edmtour40 / edmt40)
edmtour40per = Format(edmtour40per, "0%")
edmtourper = (edmtour20 + edmtour40 + edmt53) / edmtTotal
edmtourper = Format(edmtourper, "0%")
ActiveCell.Offset(-1, 0).Select
Selection.Value = " Edmonton " & edmt20 & " - 20's & " & edmt40 & " -
40's & " & edmt53 & " - NFFU's"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
If edmtcn20 = 0 And edmtcn40 = 0 And edmtcp20 = 0 And edmtcp40 = 0 Then
Selection.Value = "100% of the equipment supplied by us"
Else
Selection.Value = edmtour20per & " of the 20' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = edmtour40per & " of the 40' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = "We supplied " & edmtourper & " of the equipment sent
into Edmonton"
End If
ActiveCell.Offset(1, 0).Select
Do
If ActiveCell.Offset(1, 0) <> "" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset <> ""
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "C"), Cells(EndRow, "C"))
van20 = Application.SumIf(CriteriaRange, "=20", SumRange)
van40 = Application.SumIf(CriteriaRange, "=40", SumRange)
van53 = InputBox("How many NFFU were sent out to Vancouver?", "NFFU's to
Vancouver", "0")
vanTotal = (van20 + van40 + van53)
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "H"), Cells(EndRow, "H"))
vancn20 = Application.SumIf(CriteriaRange, "=20CNF001", SumRange)
vancn40 = Application.SumIf(CriteriaRange, "=40CNF001", SumRange)
vancp20 = Application.SumIf(CriteriaRange, "=20CPF001", SumRange)
vancp40 = Application.SumIf(CriteriaRange, "=40CPF001", SumRange)
vanour20 = (van20 - vancn20 - vancp20)
vanour40 = (van40 - vancn40 - vancp40)
If van20 <> 0 Then
vanour20per = (vanour20 / van20)
vanour20per = Format(vanour20per, "0%")
Else
vanour20per = "0%"
End If
vanour40per = (vanour40 / van40)
vanour40per = Format(vanour40per, "0%")
vanourper = (vanour20 + vanour40 + van53) / vanTotal
vanourper = Format(vanourper, "0%")
ActiveCell.Offset(-1, 0).Select
Selection.Value = " Vancouver " & van20 & " - 20's & " & van40 & " -
40's & " & van53 & " - NFFU's"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
If vancn20 = 0 And vancn40 = 0 And vancp20 = 0 And vancp40 = 0 Then
Selection.Value = "100% of the equipment supplied by us"
Else
Selection.Value = vanour20per & " of the 20' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = vanour40per & " of the 40' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = "We supplied " & vanourper & " of the equipment sent
into Vancouver"
End If
ActiveCell.Offset(1, 0).Select
Do
If ActiveCell.Offset(1, 0) <> "" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset <> ""
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "C"), Cells(EndRow, "C"))
reg20 = Application.SumIf(CriteriaRange, "=20", SumRange)
reg40 = Application.SumIf(CriteriaRange, "=40", SumRange)
regTotal = (reg20 + reg40)
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "H"), Cells(EndRow, "H"))
regcn20 = Application.SumIf(CriteriaRange, "=20CNF001", SumRange)
regcn40 = Application.SumIf(CriteriaRange, "=40CNF001", SumRange)
regcp20 = Application.SumIf(CriteriaRange, "=20CPF001", SumRange)
regcp40 = Application.SumIf(CriteriaRange, "=40CPF001", SumRange)
regour20 = (reg20 - regn20 - regcp20)
regour40 = (reg40 - regcn40 - regcp40)
If reg20 <> 0 Then
regour20per = (regour20 / reg20)
regour20per = Format(regour20per, "0%")
Else
regour20per = "0%"
End If
regour40per = (regour40 / reg40)
regour40per = Format(regour40per, "0%")
regourper = (regour20 + regour40) / regTotal
regourper = Format(regourper, "0%")
ActiveCell.Offset(-1, 0).Select
Selection.Value = " Regina " & reg20 & " - 20's & " & reg40 & " - 40's "
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
If regour20 = 0 And regour40 = 0 Then
Selection.Value = "0% of the equipment supplied by us"
Else
Selection.Value = regour20per & " of the 20' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = regour40per & " of the 40' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = "We supplied " & regourper & " of the equipment sent
into Regina"
End If
ActiveCell.Offset(1, 0).Select
Do
If ActiveCell.Offset(1, 0) <> "" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset <> ""
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "C"), Cells(EndRow, "C"))
hali20 = Application.SumIf(CriteriaRange, "=20", SumRange)
hali40 = Application.SumIf(CriteriaRange, "=40", SumRange)
haliTotal = (hali20 + hali40)
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "H"), Cells(EndRow, "H"))
halicn20 = Application.SumIf(CriteriaRange, "=20CNF001", SumRange)
halicn40 = Application.SumIf(CriteriaRange, "=40CNF001", SumRange)
halicp20 = Application.SumIf(CriteriaRange, "=20CPF001", SumRange)
halicp40 = Application.SumIf(CriteriaRange, "=40CPF001", SumRange)
haliour20 = (hali20 - halicn20 - halicp20)
haliour40 = (hali40 - halicn40 - halicp40)
If hali20 <> 0 Then
haliour20per = (haliour20 / hali20)
haliour20per = Format(haliour20per, "0%")
Else
haliour20per = "0%"
End If
haliour40per = (haliour40 / hali40)
haliour40per = Format(haliour40per, "0%")
haliourper = (haliour20 + haliour40) / haliTotal
haliourper = Format(haliourper, "0%")
ActiveCell.Offset(-1, 0).Select
Selection.Value = " Halifax " & hali20 & " - 20's & " & hali40 & " -
40's "
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
If halicn20 = 0 And halicn40 = 0 And halicp20 = 0 And halicp40 = 0 Then
Selection.Value = "100% of the equipment supplied by us"
Else
Selection.Value = haliour20per & " of the 20' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = haliour40per & " of the 40' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = "We supplied " & haliourper & " of the equipment sent
into Halifax"
End If
ActiveCell.Offset(1, 0).Select
Do
If ActiveCell.Offset(1, 0) <> "" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset <> ""
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "C"), Cells(EndRow, "C"))
atln20 = Application.SumIf(CriteriaRange, "=20", SumRange)
atln40 = Application.SumIf(CriteriaRange, "=40", SumRange)
atlnTotal = (atln20 + atln40)
FirstRow = ActiveCell.Row
EndRow = Cells(FirstRow, "B").End(xlDown).Row
Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B"))
Set CriteriaRange = Range(Cells(FirstRow, "H"), Cells(EndRow, "H"))
atlncn20 = Application.SumIf(CriteriaRange, "=20CNF001", SumRange)
atlncn40 = Application.SumIf(CriteriaRange, "=40CNF001", SumRange)
atlncp20 = Application.SumIf(CriteriaRange, "=20CPF001", SumRange)
atlncp40 = Application.SumIf(CriteriaRange, "=40CPF001", SumRange)
atlnour20 = (atln20 - atlncn20 - atlncp20)
atlnour40 = (atln40 - atlncn40 - atlncp40)
If hali20 <> 0 Then
atlnour20per = (atlnour20 / atln20)
atlnour20per = Format(atlnour20per, "0%")
Else
atlnour20per = "0%"
End If
atlnour40per = (atlnour40 / atln40)
atlnour40per = Format(atlnour40per, "0%")
atlnourper = (atlnour20 + atlnour40) / atlnTotal
atlnourper = Format(atlnourper, "0%")
ActiveCell.Offset(-1, 0).Select
Selection.Value = " Moncton " & atln20 & " - 20's & " & atln40 & " -
40's "
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
If atlncn20 = 0 And atlncn40 = 0 And atlncp20 = 0 And atlncp40 = 0 Then
Selection.Value = "100% of the equipment supplied by us"
Else
Selection.Value = atlnour20per & " of the 20' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = atlnour40per & " of the 40' equipment supplied by us"
ActiveCell.Offset(1, 0).Select
Selection.Value = "We supplied " & atlnourper & " of the equipment sent
into Moncton"
End If
LaneSpecific = (saskour20 + saskour40 + sask53 + winnour20 + winnour40 +
winn53 + calgour20 + calgour40 + calg53 + edmtour20 + edmtour40 + edmt53 +
vanour20 + vanour40 + van53 + regour20 + regour40 + haliour20 + haliour40 +
atlnour20 + atlnour40)
our20total = (saskour20 + winnour20 + calgour20 + edmtour20 + vanour20 +
regour20 + haliour20 + atlnour20)
our40total = (saskour40 + winnour40 + calgour40 + edmtour40 + vanour40 +
regour40 + haliour40 + atlnour40)
our53total = (sask53 + winn53 + calg53 + edmt53 + van53)
CN20TOTAL = (saskcn20 + winncn20 + calgcn20 + edmtcn20 + vancn20 +
regcn20 + halicn20 + atlncn20)
CN40TOTAL = (saskcn40 + winncn40 + calgcn40 + edmtcn40 + vancn40 +
regcn40 + halicn40 + atlncn40)
CP20TOTAL = (saskcp20 + winncp20 + calgcp20 + edmtcp20 + vancp20 +
regcp20 + halicp20 + atlncp20)
CP40TOTAL = (saskcp40 + winncp40 + calgcp40 + edmtcp40 + vancp40 +
regcp40 + halicp40 + atlncp40)
total = (our20total + our40total + our53total + CN20TOTAL + CN40TOTAL +
CP20TOTAL + CP40TOTAL)
total20 = (our20total + CN20TOTAL + CP20TOTAL)
total40 = (our40total + CN40TOTAL + CP40TOTAL)
our20totalper = (our20total / total20)
our20totalper = Format(our20totalper, "0%")
our40totalper = (our40total / total40)
our40totalper = Format(our40totalper, "0%")
cn20totalper = (CN20TOTAL / total20)
cn20totalper = Format(cn20totalper, "0%")
cn40totalper = (CN40TOTAL / total40)
cn40totalper = Format(cn40totalper, "0%")
cp20totalper = (CP20TOTAL / total20)
cp20totalper = Format(cp20totalper, "0%")
cp40totalper = (CP40TOTAL / total40)
cp40totalper = Format(cp40totalper, "0%")
LaneSpecificPer = (LaneSpecific / total)
LaneSpecificPer = Format(LaneSpecificPer, "0%")
PU = Application.CountIf(Worksheets("sheet1").Range("e:e"), "*-p*")
putotal = (PU + CN20TOTAL + CN40TOTAL + CP20TOTAL + CP40TOTAL)
ccsi = (total - putotal)
ccsiper = (ccsi / total)
ccsiper = Format(ccsiper, "0%")
ActiveCell.Offset(2, 0).Select
Selection.Value = LaneSpecific & " lane specific containers shipped out
last week including " & our53total & " -NFFU's"
ActiveCell.Offset(1, 0).Select
Selection.Value = LaneSpecificPer & " of the equipment shipped generated
revenue"
ActiveCell.Offset(2, 0).Select
Selection.Value = "20' - " & our20totalper & " supplied by us / " &
cn20totalper & " supplied by CN / " & cp20totalper & " supplied by CP (" &
our20total & "/" & CN20TOTAL & "/" & CP20TOTAL & ")"
ActiveCell.Offset(1, 0).Select
Selection.Value = "40' - " & our40totalper & " supplied by us / " &
cn40totalper & " supplied by CN / " & cp40totalper & " supplied by CP (" &
our40total & "/" & CN40TOTAL & "/" & CP40TOTAL & ")"
ActiveCell.Offset(1, 0).Select
Selection.Value = "CCSI " & ccsiper & " (" & ccsi & "/" & putotal & ")"
'Calculating totals
Columns("D
").ColumnWidth = 50
'autofit column D
Cells.Replace What:="SASK", Replacement:=" -",
LookAt:=xlWhole, MatchCase:=True
Cells.Replace What:="WINN", Replacement:=" -",
LookAt:=xlWhole, MatchCase:=True
Cells.Replace What:="CALG", Replacement:=" -",
LookAt:=xlWhole, MatchCase:=True
Cells.Replace What:="EDMT", Replacement:=" -",
LookAt:=xlWhole, MatchCase:=True
Cells.Replace What:="VAN", Replacement:=" -",
LookAt:=xlWhole, MatchCase:=True
Cells.Replace What:="REG", Replacement:=" -",
LookAt:=xlWhole, MatchCase:=True
Cells.Replace What:="HALI", Replacement:=" -",
LookAt:=xlWhole, MatchCase:=True
Cells.Replace What:="ATLN", Replacement:=" -",
LookAt:=xlWhole, MatchCase:=True
'deleting words SASK,WINN,CALG,EDMT,VAN,REG,HALI and ATLN and replacing
for -
Columns("B:C").Select
With Selection
.HorizontalAlignment = xlCenter
End With
'centering data
End Sub
