VBA Help

S

Spartin

New Member
Joined
Feb 25, 2014
Messages
35
Good afternoon:

We recently lost our VBA guru in the office to another employer, leaving me holding the bag. I am still reading my "VBA for Dummies" book. As luck would have it, a coworker has an issue with a macro created by the departed individual. In short, the macro disappeared from her worksheet. The creator left code for the macro in a text file, so I figured I could simply create a new macro and copy/paste the code from the text file, but when I do that I get an error: "Compile Error: Invalid Outside Procedure" Was hoping someone could point out any obvious mistakes I am making. More detail below:

1. In the file, I clicked the "Macros" button in the Developer tab and created a new macro, "Country_Fair"
2. The VB window opened opened with the following code already showing:

Sub Country_Fair()

End Sub

3. I then copied the code for the macro from the text file and pasted it in the VB window.
4. I then run the macro and get the error, and "A1" is highlighted (for some reason I cannot highlight the text in this post) as such:

If IsEmpty(Range("A1").Value) = True Then


The full code is below. Any ideas as to how to fix this?

Sub Country_Fair()
Dim Counter As Long
Dim AccCounter As Long
Dim SecondStops As Long
Dim Stps As Long
Dim SplitOne As String
Dim SplitTwo As String
Dim SplitThree As String
Dim Inv As Worksheet
Dim Detail As Worksheet
Dim MinAns As Long
Dim NewMin As Double
Dim OrdNumImg As String
Dim Prev As Long
Dim Matcher As Long
Dim FSC As Double
Dim FSCC As Double
Dim LineHaul As Double



Set Detail = Worksheets(1)
Detail.Name = "Load Details"
' Turn off Screen Updating for speed
Application.ScreenUpdating = False
' Get rid of Useless columns
If IsEmpty(Range("A1").Value) = True Then
Columns("A:A").Delete Shift:=xlToLeft
Rows("1:1").Delete Shift:=xlUp
End If
' Count how many row down the document is
Counter = Cells(Rows.Count, "A").End(xlUp).Row
AccCounter = Worksheets("Accessorial Charges").Cells(Rows.Count, "A").End(xlUp).Row
' Format the Acc Charge Tab to match the country fair sheet
Worksheets("Accessorial Charges").Columns("C:C").Cut
Worksheets("Accessorial Charges").Columns("B:B").Insert Shift:=xlToRight
Worksheets("Accessorial Charges").Columns("C:C").Delete
Worksheets("Accessorial Charges").Columns("C:C").Delete
Worksheets("Accessorial Charges").Columns("D:D").Delete
Worksheets("Accessorial Charges").Columns("Z:Z").Delete
Worksheets("Accessorial Charges").Columns("AC:AC").Delete
Worksheets("Accessorial Charges").Columns("AF:AF").Delete
Worksheets("Accessorial Charges").Columns("M:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Worksheets("Accessorial Charges").Range("M1").Value = "FURRAT"
Worksheets("Accessorial Charges").Columns("AG:AG").Delete
Worksheets("Accessorial Charges").Columns("AG:AG").Delete
Worksheets("Accessorial Charges").Columns("AG:AG").Delete
Worksheets("Accessorial Charges").Columns("AG:AG").Delete
Worksheets("Accessorial Charges").Columns("Z:Z").Cut
Worksheets("Accessorial Charges").Columns("Y:Y").Insert Shift:=xlToRight
Worksheets("Accessorial Charges").Columns("AC:AC").Cut
Worksheets("Accessorial Charges").Columns("AB:AB").Insert Shift:=xlToRight
Worksheets("Accessorial Charges").Columns("AF:AF").Cut
Worksheets("Accessorial Charges").Columns("AE:AE").Insert Shift:=xlToRight
Worksheets("Accessorial Charges").Columns("W:W").Cut
Worksheets("Accessorial Charges").Columns("S:S").Insert Shift:=xlToRight
Worksheets("Accessorial Charges").Columns("U:U").Cut
Worksheets("Accessorial Charges").Columns("T:T").Insert Shift:=xlToRight
Range("A2:AF" & Counter).Sort Key1:=Columns("D"), Order1:=xlAscending, Key2:=Columns("A"), Order2:=xlAscending
' Split out the invoice numbers from the invoice letters
Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Worksheets("Accessorial Charges").Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Application.DisplayAlerts = False
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1))
Worksheets("Accessorial Charges").Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1))
Application.DisplayAlerts = True
Range("B1") = "#"
Worksheets("Accessorial Charges").Range("B1") = "#"
' Search for missing A invoices and copy the missing ones in
For Stps = Counter To 2 Step -1
Prev = Stps - 1
If Range("B" & Stps).Value = "B" And Range("B" & Prev).Value <> "A" Then
For Matcher = 2 To AccCounter
If Worksheets("Accessorial Charges").Range("A" & Matcher) = Range("A" & Stps) Then
Worksheets("Accessorial Charges").Rows(Matcher & ":" & Matcher).Copy
Rows(Stps & ":" & Stps).Insert Shift:=xlDown
End If
Next
End If
Next
Columns("M:M").Delete Shift:=xlToLeft
Columns("Q:Q").Delete Shift:=xlToLeft
' Recount the Rows
Counter = Cells(Rows.Count, "A").End(xlUp).Row
' Ask the user for confirmation on all minimum charges
For Stps = 2 To Counter
If Cells(Stps, 15).Value > 0 Then
' Display the images of the order number in question
OrdNumImg = Cells(Stps, 1)
Shell "C:\Program Files (x86)\Pegasus TransTech\QuickView\Transflo.QuickView.Launcher.exe", vbNormalFocus
Application.Wait Now + TimeValue("00:00:02")
Application.SendKeys ("{TAB}")
Application.SendKeys (OrdNumImg)
Application.SendKeys ("~")
Application.SendKeys ("{NUMLOCK}")
' Ask the user if Min Charge is correct, correct it if wrong and then correct the FSC as a result
MinAns = MsgBox("Order # " & Cells(Stps, 1) & " has a Min charge of $" & Cells(Stps, 15) & vbNewLine & "Is this Min Charge Correct?" _
, vbYesNo + vbQuestion, "Minimum Gallons Charge Confirmation")
If MinAns = vbNo Then
FSC = WorksheetFunction.Round(Cells(Stps - 1, 12).Value / (Cells(Stps - 2, 10).Value + Cells(Stps, 13).Value + Cells(Stps, 15).Value), 3)
NewMin = InputBox("What should be the Min charge on this load?", "Minimum Gallons Charge Correction")
Cells(Stps, 15) = NewMin
FSCC = WorksheetFunction.Round((Cells(Stps - 2, 10) + Cells(Stps, 13) + Cells(Stps, 15)) * FSC, 2)
MsgBox "This will change the FSC Charge on this load from $" & Cells(Stps - 1, 12) & " to $" & FSCC & ". (The FSC that applies to this load is " & FSC & ".)"
Cells(Stps - 1, 12) = FSCC
End If
End If
Next Stps
' Ask the user for confirmation on all unloading detention charges
For Stps = 2 To Counter
If Cells(Stps, 21).Value > 0 Then
' Display the images of the order number in question
OrdNumImg = Cells(Stps, 1)
Shell "C:\Program Files (x86)\Pegasus TransTech\QuickView\Transflo.QuickView.Launcher.exe", vbNormalFocus
Application.Wait Now + TimeValue("00:00:02")
Application.SendKeys ("{TAB}" & OrdNumImg & "~")
Application.SendKeys ("{NUMLOCK}")
' Ask the user if Det Charge is correct
MinAns = MsgBox("Order # " & Cells(Stps, 1) & " has a Unloading Detention charge of $" & Cells(Stps, 21) & vbNewLine _
& "Is this Unloading Detention Charge Correct?", vbYesNo + vbQuestion, "Unloading Detention Charge Confirmation")
If MinAns = vbNo Then
NewMin = InputBox("What should be the charge on this load?" & vbNewLine & "(Country Fair standard is currently $60/h)", "Unloading Detention Correction")
Cells(Stps, 21) = NewMin
End If
End If
Next Stps
' Change all stop charges over $45 out of Warren and Cory to just $45
For Stps = 2 To Counter
If Cells(Stps, 6).Value = "PA" Or Cells(Stps, 6).Value = "OH" Then
If Cells(Stps, 22).Value > 45 Then
Cells(Stps, 22).Value = 45
End If
End If
Next Stps
' Make a column for the splits
Range("F:F").Copy Range("AF:AF")
Range("AF:AF").ClearContents
Range("AF1").Value = "Stores"
Range("F:F").Copy Range("AG:AG")
Range("AG:AG").ClearContents
Range("AG1").Value = "Approved/Not Approved"
' Find out how many splits there are
Range("A1:AG" & Counter).Sort Key1:=Columns("Z"), Order1:=xlAscending, Header:=xlYes
SecondStops = Cells(Rows.Count, "Z").End(xlUp).Row
' Fill in the store splits column
Range("AF2").Formula = "=CONCATENATE(W2,""/"",Z2)"
Range("AF2").AutoFill Destination:=Range("AF2:AF" & SecondStops), Type:=xlFillValues
Range("AF2:AF" & SecondStops).Copy
Range("AF2:AF" & SecondStops).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
' Mark all allowed Stop1/Stop2 combos as Approved splits and mark all the rest (out of PA) as Not Approved
For Stps = 2 To SecondStops
If Cells(Stps, 6).Value = "PA" Or Cells(Stps, 6).Value = "OH" Then
SplitOne = Range("W" & Stps) & "/" & Range("Z" & Stps)
Select Case SplitOne
Case "COUNF9/COUNF34": Range("AG" & Stps).Value = "Approved"
Case "COUNF1/COUNF35": Range("AG" & Stps).Value = "Approved"
Case "COUNF2/COUNF59": Range("AG" & Stps).Value = "Approved"
Case "COUNF10/COUNF63": Range("AG" & Stps).Value = "Approved"
Case "COUNF1/COUNF73": Range("AG" & Stps).Value = "Approved"
Case "COUNF7/COUNF82": Range("AG" & Stps).Value = "Approved"
Case "COUNF3/COUNF83": Range("AG" & Stps).Value = "Approved"
Case "COUNF3/COUNF90": Range("AG" & Stps).Value = "Approved"
Case "COUNF12/COUNF95": Range("AG" & Stps).Value = "Approved"
Case "COUNF7/COUNF15": Range("AG" & Stps).Value = "Approved"
Case "COUNF8/COUNF31": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF101": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF40": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF15/COUNF7": Range("AG" & Stps).Value = "Approved"
Case "COUNF18/COUNF29": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF26/COUNF29": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF101": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF40": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF28/COUNF36": Range("AG" & Stps).Value = "Approved"
Case "COUNF29/COUNF18": Range("AG" & Stps).Value = "Approved"
Case "COUNF29/COUNF26": Range("AG" & Stps).Value = "Approved"
Case "COUNF30/COUNF55": Range("AG" & Stps).Value = "Approved"
Case "COUNF31/COUNF8": Range("AG" & Stps).Value = "Approved"
Case "COUNF31/COUNF95": Range("AG" & Stps).Value = "Approved"
Case "COUNF32/COUNF33": Range("AG" & Stps).Value = "Approved"
Case "COUNF33/COUNF32": Range("AG" & Stps).Value = "Approved"
Case "COUNF34/COUNF9": Range("AG" & Stps).Value = "Approved"
Case "COUNF35/COUNF1": Range("AG" & Stps).Value = "Approved"
Case "COUNF35/COUNF73": Range("AG" & Stps).Value = "Approved"
Case "COUNF36/COUNF28": Range("AG" & Stps).Value = "Approved"
Case "COUNF38/COUNF61": Range("AG" & Stps).Value = "Approved"
Case "COUNF38/COUNF91": Range("AG" & Stps).Value = "Approved"
Case "COUNF40/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF40/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF40/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF41/COUNF42": Range("AG" & Stps).Value = "Approved"
Case "COUNF41/COUNF46": Range("AG" & Stps).Value = "Approved"
Case "COUNF42/COUNF41": Range("AG" & Stps).Value = "Approved"
Case "COUNF42/COUNF46": Range("AG" & Stps).Value = "Approved"
Case "COUNF43/COUNF81": Range("AG" & Stps).Value = "Approved"
Case "COUNF44/COUNF93": Range("AG" & Stps).Value = "Approved"
Case "COUNF44/COUNF99": Range("AG" & Stps).Value = "Approved"
Case "COUNF45/COUNF58": Range("AG" & Stps).Value = "Approved"
Case "COUNF46/COUNF41": Range("AG" & Stps).Value = "Approved"
Case "COUNF46/COUNF42": Range("AG" & Stps).Value = "Approved"
Case "COUNF47/COUNF89": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF2": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF50": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF56": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF72": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF50/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF50/COUNF56": Range("AG" & Stps).Value = "Approved"
Case "COUNF50/COUNF72": Range("AG" & Stps).Value = "Approved"
Case "COUNF55/COUNF30": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF2": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF50": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF72": Range("AG" & Stps).Value = "Approved"
Case "COUNF58/COUNF45": Range("AG" & Stps).Value = "Approved"
Case "COUNF59/COUNF2": Range("AG" & Stps).Value = "Approved"
Case "COUNF60/COUNF67": Range("AG" & Stps).Value = "Approved"
Case "COUNF60/COUNF94": Range("AG" & Stps).Value = "Approved"
Case "COUNF61/COUNF38": Range("AG" & Stps).Value = "Approved"
Case "COUNF61/COUNF91": Range("AG" & Stps).Value = "Approved"
Case "COUNF62/COUNF92": Range("AG" & Stps).Value = "Approved"
Case "COUNF63/COUNF10": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF101": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF40": Range("AG" & Stps).Value = "Approved"
Case "COUNF67/COUNF60": Range("AG" & Stps).Value = "Approved"
Case "COUNF67/COUNF94": Range("AG" & Stps).Value = "Approved"
Case "COUNF72/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF72/COUNF50": Range("AG" & Stps).Value = "Approved"
Case "COUNF72/COUNF56": Range("AG" & Stps).Value = "Approved"
Case "COUNF73/COUNF1": Range("AG" & Stps).Value = "Approved"
Case "COUNF73/COUNF35": Range("AG" & Stps).Value = "Approved"
Case "COUNF74/COUNF85": Range("AG" & Stps).Value = "Approved"
Case "COUNF75/COUNF77": Range("AG" & Stps).Value = "Approved"
Case "COUNF76/COUNF78": Range("AG" & Stps).Value = "Approved"
Case "COUNF77/COUNF75": Range("AG" & Stps).Value = "Approved"
Case "COUNF78/COUNF76": Range("AG" & Stps).Value = "Approved"
Case "COUNF81/COUNF43": Range("AG" & Stps).Value = "Approved"
Case "COUNF82/COUNF7": Range("AG" & Stps).Value = "Approved"
Case "COUNF83/COUNF3": Range("AG" & Stps).Value = "Approved"
Case "COUNF83/COUNF90": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF85/COUNF74": Range("AG" & Stps).Value = "Approved"
Case "COUNF89/COUNF47": Range("AG" & Stps).Value = "Approved"
Case "COUNF90/COUNF3": Range("AG" & Stps).Value = "Approved"
Case "COUNF90/COUNF83": Range("AG" & Stps).Value = "Approved"
Case "COUNF91/COUNF38": Range("AG" & Stps).Value = "Approved"
Case "COUNF91/COUNF61": Range("AG" & Stps).Value = "Approved"
Case "COUNF92/COUNF62": Range("AG" & Stps).Value = "Approved"
Case "COUNF93/COUNF44": Range("AG" & Stps).Value = "Approved"
Case "COUNF93/COUNF99": Range("AG" & Stps).Value = "Approved"
Case "COUNF94/COUNF60": Range("AG" & Stps).Value = "Approved"
Case "COUNF94/COUNF67": Range("AG" & Stps).Value = "Approved"
Case "COUNF95/COUNF12": Range("AG" & Stps).Value = "Approved"
Case "COUNF95/COUNF31": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF99/COUNF44": Range("AG" & Stps).Value = "Approved"
Case "COUNF99/COUNF93": Range("AG" & Stps).Value = "Approved"
Case "COUNF101/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF101/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF101/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF102/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF102/COUNF56": Range("AG" & Stps).Value = "Approved"
Case Else: Range("AG" & Stps).Value = "Not Approved"
End Select
' Make sure Stop1/Stop3 Combinations aren't hiding any approved splits
If Cells(Stps, 33).Value = "Not Approved" Then
SplitTwo = Range("W" & Stps) & "/" & Range("AC" & Stps)
Select Case SplitTwo
Case "COUNF9/COUNF34": Range("AG" & Stps).Value = "Approved"
Case "COUNF1/COUNF35": Range("AG" & Stps).Value = "Approved"
Case "COUNF2/COUNF59": Range("AG" & Stps).Value = "Approved"
Case "COUNF10/COUNF63": Range("AG" & Stps).Value = "Approved"
Case "COUNF1/COUNF73": Range("AG" & Stps).Value = "Approved"
Case "COUNF7/COUNF82": Range("AG" & Stps).Value = "Approved"
Case "COUNF3/COUNF83": Range("AG" & Stps).Value = "Approved"
Case "COUNF3/COUNF90": Range("AG" & Stps).Value = "Approved"
Case "COUNF12/COUNF95": Range("AG" & Stps).Value = "Approved"
Case "COUNF7/COUNF15": Range("AG" & Stps).Value = "Approved"
Case "COUNF8/COUNF31": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF101": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF40": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF15/COUNF7": Range("AG" & Stps).Value = "Approved"
Case "COUNF18/COUNF29": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF26/COUNF29": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF101": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF40": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF28/COUNF36": Range("AG" & Stps).Value = "Approved"
Case "COUNF29/COUNF18": Range("AG" & Stps).Value = "Approved"
Case "COUNF29/COUNF26": Range("AG" & Stps).Value = "Approved"
Case "COUNF30/COUNF55": Range("AG" & Stps).Value = "Approved"
Case "COUNF31/COUNF8": Range("AG" & Stps).Value = "Approved"
Case "COUNF31/COUNF95": Range("AG" & Stps).Value = "Approved"
Case "COUNF32/COUNF33": Range("AG" & Stps).Value = "Approved"
Case "COUNF33/COUNF32": Range("AG" & Stps).Value = "Approved"
Case "COUNF34/COUNF9": Range("AG" & Stps).Value = "Approved"
Case "COUNF35/COUNF1": Range("AG" & Stps).Value = "Approved"
Case "COUNF35/COUNF73": Range("AG" & Stps).Value = "Approved"
Case "COUNF36/COUNF28": Range("AG" & Stps).Value = "Approved"
Case "COUNF38/COUNF61": Range("AG" & Stps).Value = "Approved"
Case "COUNF38/COUNF91": Range("AG" & Stps).Value = "Approved"
Case "COUNF40/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF40/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF40/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF41/COUNF42": Range("AG" & Stps).Value = "Approved"
Case "COUNF41/COUNF46": Range("AG" & Stps).Value = "Approved"
Case "COUNF42/COUNF41": Range("AG" & Stps).Value = "Approved"
Case "COUNF42/COUNF46": Range("AG" & Stps).Value = "Approved"
Case "COUNF43/COUNF81": Range("AG" & Stps).Value = "Approved"
Case "COUNF44/COUNF93": Range("AG" & Stps).Value = "Approved"
Case "COUNF44/COUNF99": Range("AG" & Stps).Value = "Approved"
Case "COUNF45/COUNF58": Range("AG" & Stps).Value = "Approved"
Case "COUNF46/COUNF41": Range("AG" & Stps).Value = "Approved"
Case "COUNF46/COUNF42": Range("AG" & Stps).Value = "Approved"
Case "COUNF47/COUNF89": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF2": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF50": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF56": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF72": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF50/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF50/COUNF56": Range("AG" & Stps).Value = "Approved"
Case "COUNF50/COUNF72": Range("AG" & Stps).Value = "Approved"
Case "COUNF55/COUNF30": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF2": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF50": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF72": Range("AG" & Stps).Value = "Approved"
Case "COUNF58/COUNF45": Range("AG" & Stps).Value = "Approved"
Case "COUNF59/COUNF2": Range("AG" & Stps).Value = "Approved"
Case "COUNF60/COUNF67": Range("AG" & Stps).Value = "Approved"
Case "COUNF60/COUNF94": Range("AG" & Stps).Value = "Approved"
Case "COUNF61/COUNF38": Range("AG" & Stps).Value = "Approved"
Case "COUNF61/COUNF91": Range("AG" & Stps).Value = "Approved"
Case "COUNF62/COUNF92": Range("AG" & Stps).Value = "Approved"
Case "COUNF63/COUNF10": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF101": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF40": Range("AG" & Stps).Value = "Approved"
Case "COUNF67/COUNF60": Range("AG" & Stps).Value = "Approved"
Case "COUNF67/COUNF94": Range("AG" & Stps).Value = "Approved"
Case "COUNF72/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF72/COUNF50": Range("AG" & Stps).Value = "Approved"
Case "COUNF72/COUNF56": Range("AG" & Stps).Value = "Approved"
Case "COUNF73/COUNF1": Range("AG" & Stps).Value = "Approved"
Case "COUNF73/COUNF35": Range("AG" & Stps).Value = "Approved"
Case "COUNF74/COUNF85": Range("AG" & Stps).Value = "Approved"
Case "COUNF75/COUNF77": Range("AG" & Stps).Value = "Approved"
Case "COUNF76/COUNF78": Range("AG" & Stps).Value = "Approved"
Case "COUNF77/COUNF75": Range("AG" & Stps).Value = "Approved"
Case "COUNF78/COUNF76": Range("AG" & Stps).Value = "Approved"
Case "COUNF81/COUNF43": Range("AG" & Stps).Value = "Approved"
Case "COUNF82/COUNF7": Range("AG" & Stps).Value = "Approved"
Case "COUNF83/COUNF3": Range("AG" & Stps).Value = "Approved"
Case "COUNF83/COUNF90": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF85/COUNF74": Range("AG" & Stps).Value = "Approved"
Case "COUNF89/COUNF47": Range("AG" & Stps).Value = "Approved"
Case "COUNF90/COUNF3": Range("AG" & Stps).Value = "Approved"
Case "COUNF90/COUNF83": Range("AG" & Stps).Value = "Approved"
Case "COUNF91/COUNF38": Range("AG" & Stps).Value = "Approved"
Case "COUNF91/COUNF61": Range("AG" & Stps).Value = "Approved"
Case "COUNF92/COUNF62": Range("AG" & Stps).Value = "Approved"
Case "COUNF93/COUNF44": Range("AG" & Stps).Value = "Approved"
Case "COUNF93/COUNF99": Range("AG" & Stps).Value = "Approved"
Case "COUNF94/COUNF60": Range("AG" & Stps).Value = "Approved"
Case "COUNF94/COUNF67": Range("AG" & Stps).Value = "Approved"
Case "COUNF95/COUNF12": Range("AG" & Stps).Value = "Approved"
Case "COUNF95/COUNF31": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF99/COUNF44": Range("AG" & Stps).Value = "Approved"
Case "COUNF99/COUNF93": Range("AG" & Stps).Value = "Approved"
Case "COUNF101/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF101/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF101/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF102/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF102/COUNF56": Range("AG" & Stps).Value = "Approved"
End Select
End If
' Make sure Stop2/Stop3 Combinations aren't hiding any approved splits
If Cells(Stps, 33).Value = "Not Approved" Then
SplitThree = Range("Z" & Stps) & "/" & Range("AC" & Stps)
Select Case SplitThree
Case "COUNF9/COUNF34": Range("AG" & Stps).Value = "Approved"
Case "COUNF1/COUNF35": Range("AG" & Stps).Value = "Approved"
Case "COUNF2/COUNF59": Range("AG" & Stps).Value = "Approved"
Case "COUNF10/COUNF63": Range("AG" & Stps).Value = "Approved"
Case "COUNF1/COUNF73": Range("AG" & Stps).Value = "Approved"
Case "COUNF7/COUNF82": Range("AG" & Stps).Value = "Approved"
Case "COUNF3/COUNF83": Range("AG" & Stps).Value = "Approved"
Case "COUNF3/COUNF90": Range("AG" & Stps).Value = "Approved"
Case "COUNF12/COUNF95": Range("AG" & Stps).Value = "Approved"
Case "COUNF7/COUNF15": Range("AG" & Stps).Value = "Approved"
Case "COUNF8/COUNF31": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF101": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF40": Range("AG" & Stps).Value = "Approved"
Case "COUNF14/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF15/COUNF7": Range("AG" & Stps).Value = "Approved"
Case "COUNF18/COUNF29": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF25/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF26/COUNF29": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF101": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF40": Range("AG" & Stps).Value = "Approved"
Case "COUNF27/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF28/COUNF36": Range("AG" & Stps).Value = "Approved"
Case "COUNF29/COUNF18": Range("AG" & Stps).Value = "Approved"
Case "COUNF29/COUNF26": Range("AG" & Stps).Value = "Approved"
Case "COUNF30/COUNF55": Range("AG" & Stps).Value = "Approved"
Case "COUNF31/COUNF8": Range("AG" & Stps).Value = "Approved"
Case "COUNF31/COUNF95": Range("AG" & Stps).Value = "Approved"
Case "COUNF32/COUNF33": Range("AG" & Stps).Value = "Approved"
Case "COUNF33/COUNF32": Range("AG" & Stps).Value = "Approved"
Case "COUNF34/COUNF9": Range("AG" & Stps).Value = "Approved"
Case "COUNF35/COUNF1": Range("AG" & Stps).Value = "Approved"
Case "COUNF35/COUNF73": Range("AG" & Stps).Value = "Approved"
Case "COUNF36/COUNF28": Range("AG" & Stps).Value = "Approved"
Case "COUNF38/COUNF61": Range("AG" & Stps).Value = "Approved"
Case "COUNF38/COUNF91": Range("AG" & Stps).Value = "Approved"
Case "COUNF40/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF40/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF40/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF41/COUNF42": Range("AG" & Stps).Value = "Approved"
Case "COUNF41/COUNF46": Range("AG" & Stps).Value = "Approved"
Case "COUNF42/COUNF41": Range("AG" & Stps).Value = "Approved"
Case "COUNF42/COUNF46": Range("AG" & Stps).Value = "Approved"
Case "COUNF43/COUNF81": Range("AG" & Stps).Value = "Approved"
Case "COUNF44/COUNF93": Range("AG" & Stps).Value = "Approved"
Case "COUNF44/COUNF99": Range("AG" & Stps).Value = "Approved"
Case "COUNF45/COUNF58": Range("AG" & Stps).Value = "Approved"
Case "COUNF46/COUNF41": Range("AG" & Stps).Value = "Approved"
Case "COUNF46/COUNF42": Range("AG" & Stps).Value = "Approved"
Case "COUNF47/COUNF89": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF2": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF50": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF56": Range("AG" & Stps).Value = "Approved"
Case "COUNF48/COUNF72": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF49/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF50/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF50/COUNF56": Range("AG" & Stps).Value = "Approved"
Case "COUNF50/COUNF72": Range("AG" & Stps).Value = "Approved"
Case "COUNF55/COUNF30": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF2": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF50": Range("AG" & Stps).Value = "Approved"
Case "COUNF56/COUNF72": Range("AG" & Stps).Value = "Approved"
Case "COUNF58/COUNF45": Range("AG" & Stps).Value = "Approved"
Case "COUNF59/COUNF2": Range("AG" & Stps).Value = "Approved"
Case "COUNF60/COUNF67": Range("AG" & Stps).Value = "Approved"
Case "COUNF60/COUNF94": Range("AG" & Stps).Value = "Approved"
Case "COUNF61/COUNF38": Range("AG" & Stps).Value = "Approved"
Case "COUNF61/COUNF91": Range("AG" & Stps).Value = "Approved"
Case "COUNF62/COUNF92": Range("AG" & Stps).Value = "Approved"
Case "COUNF63/COUNF10": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF65/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF101": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF66/COUNF40": Range("AG" & Stps).Value = "Approved"
Case "COUNF67/COUNF60": Range("AG" & Stps).Value = "Approved"
Case "COUNF67/COUNF94": Range("AG" & Stps).Value = "Approved"
Case "COUNF72/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF72/COUNF50": Range("AG" & Stps).Value = "Approved"
Case "COUNF72/COUNF56": Range("AG" & Stps).Value = "Approved"
Case "COUNF73/COUNF1": Range("AG" & Stps).Value = "Approved"
Case "COUNF73/COUNF35": Range("AG" & Stps).Value = "Approved"
Case "COUNF74/COUNF85": Range("AG" & Stps).Value = "Approved"
Case "COUNF75/COUNF77": Range("AG" & Stps).Value = "Approved"
Case "COUNF76/COUNF78": Range("AG" & Stps).Value = "Approved"
Case "COUNF77/COUNF75": Range("AG" & Stps).Value = "Approved"
Case "COUNF78/COUNF76": Range("AG" & Stps).Value = "Approved"
Case "COUNF81/COUNF43": Range("AG" & Stps).Value = "Approved"
Case "COUNF82/COUNF7": Range("AG" & Stps).Value = "Approved"
Case "COUNF83/COUNF3": Range("AG" & Stps).Value = "Approved"
Case "COUNF83/COUNF90": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF84/COUNF98": Range("AG" & Stps).Value = "Approved"
Case "COUNF85/COUNF74": Range("AG" & Stps).Value = "Approved"
Case "COUNF89/COUNF47": Range("AG" & Stps).Value = "Approved"
Case "COUNF90/COUNF3": Range("AG" & Stps).Value = "Approved"
Case "COUNF90/COUNF83": Range("AG" & Stps).Value = "Approved"
Case "COUNF91/COUNF38": Range("AG" & Stps).Value = "Approved"
Case "COUNF91/COUNF61": Range("AG" & Stps).Value = "Approved"
Case "COUNF92/COUNF62": Range("AG" & Stps).Value = "Approved"
Case "COUNF93/COUNF44": Range("AG" & Stps).Value = "Approved"
Case "COUNF93/COUNF99": Range("AG" & Stps).Value = "Approved"
Case "COUNF94/COUNF60": Range("AG" & Stps).Value = "Approved"
Case "COUNF94/COUNF67": Range("AG" & Stps).Value = "Approved"
Case "COUNF95/COUNF12": Range("AG" & Stps).Value = "Approved"
Case "COUNF95/COUNF31": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF25": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF49": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF65": Range("AG" & Stps).Value = "Approved"
Case "COUNF98/COUNF84": Range("AG" & Stps).Value = "Approved"
Case "COUNF99/COUNF44": Range("AG" & Stps).Value = "Approved"
Case "COUNF99/COUNF93": Range("AG" & Stps).Value = "Approved"
Case "COUNF101/COUNF14": Range("AG" & Stps).Value = "Approved"
Case "COUNF101/COUNF27": Range("AG" & Stps).Value = "Approved"
Case "COUNF101/COUNF66": Range("AG" & Stps).Value = "Approved"
Case "COUNF102/COUNF48": Range("AG" & Stps).Value = "Approved"
Case "COUNF102/COUNF56": Range("AG" & Stps).Value = "Approved"
End Select
End If
' Check if the Not Approved splits include <10k stores
If Cells(Stps, 33).Value = "Not Approved" Then
Select Case Cells(Stps, 23)
Case "COUNF2": Range("AG" & Stps).Value = "<10k"
Case "COUNF10": Range("AG" & Stps).Value = "<10k"
Case "COUNF25": Range("AG" & Stps).Value = "<10k"
Case "COUNF43": Range("AG" & Stps).Value = "<10k"
Case "COUNF45": Range("AG" & Stps).Value = "<10k"
Case "COUNF48": Range("AG" & Stps).Value = "<10k"
Case "COUNF85": Range("AG" & Stps).Value = "<10k"
Case "COUNF94": Range("AG" & Stps).Value = "<10k"
Case "COUNF99": Range("AG" & Stps).Value = "<10k"
End Select
End If
If Cells(Stps, 33).Value = "Not Approved" Then
Select Case Cells(Stps, 26)
Case "COUNF2": Range("AG" & Stps).Value = "<10k"
Case "COUNF10": Range("AG" & Stps).Value = "<10k"
Case "COUNF25": Range("AG" & Stps).Value = "<10k"
Case "COUNF43": Range("AG" & Stps).Value = "<10k"
Case "COUNF45": Range("AG" & Stps).Value = "<10k"
Case "COUNF48": Range("AG" & Stps).Value = "<10k"
Case "COUNF85": Range("AG" & Stps).Value = "<10k"
Case "COUNF94": Range("AG" & Stps).Value = "<10k"
Case "COUNF99": Range("AG" & Stps).Value = "<10k"
End Select
End If
If Cells(Stps, 33).Value = "Not Approved" Then
Select Case Cells(Stps, 29)
Case "COUNF2": Range("AG" & Stps).Value = "<10k"
Case "COUNF10": Range("AG" & Stps).Value = "<10k"
Case "COUNF25": Range("AG" & Stps).Value = "<10k"
Case "COUNF43": Range("AG" & Stps).Value = "<10k"
Case "COUNF45": Range("AG" & Stps).Value = "<10k"
Case "COUNF48": Range("AG" & Stps).Value = "<10k"
Case "COUNF85": Range("AG" & Stps).Value = "<10k"
Case "COUNF94": Range("AG" & Stps).Value = "<10k"
Case "COUNF99": Range("AG" & Stps).Value = "<10k"
End Select
End If
Else
Range("AG" & Stps).Value = "Approved"
End If
Next Stps
' Clear the current sort and resort by invoice # and invoice letter
Range("A1:AG" & Counter).Select
Detail.Sort.SortFields.Clear
Range("A1:AG" & Counter).Sort Key1:=Columns("A"), Order1:=xlDescending, Key2:=Columns("B"), Order2:=xlAscending, Header:=xlYes
' Paste down Approved/Not Approved Status down to B and C invoices
For Stps = 2 To Counter
If WorksheetFunction.IsText(Cells(Stps, 33)) = True Then
If Cells(Stps, 1) = Cells(Stps + 1, 1) Then
Cells(Stps, 33).Copy
Cells(Stps + 1, 33).PasteSpecial Paste:=xlPasteValues
End If
End If
Next Stps
For Stps = 2 To Counter
' First if statement deals with Not Approved invoices, deleting stop charge from the Total Rev Column and UNLSTP Column, doing the same with the FRC charge
If Cells(Stps, 2) = "B" And Cells(Stps, 33) = "Not Approved" Then
Cells(Stps, 11).Value = Cells(Stps, 11).Value - Cells(Stps, 22).Value
Cells(Stps, 22).ClearContents
Cells(Stps + 1, 10).Value = Cells(Stps + 1, 10).Value - Cells(Stps + 1, 13).Value
Cells(Stps + 1, 11).Value = Cells(Stps + 1, 11).Value - Cells(Stps + 1, 13).Value
Cells(Stps + 1, 13).ClearContents
End If
' Second if statement deals with <10k splits, leaving the stop charge as is and deleting the FRC from Line Haul, Total Rev and FURRAT columns
If Cells(Stps, 2) = "B" And Cells(Stps, 33) = "<10k" Then
Cells(Stps + 1, 10).Value = Cells(Stps + 1, 10).Value - Cells(Stps + 1, 13).Value
Cells(Stps + 1, 11).Value = Cells(Stps + 1, 11).Value - Cells(Stps + 1, 13).Value
Cells(Stps + 1, 13).ClearContents
End If
Next Stps
' Clear the current sort and resort by date then invoice then #
Range("A1:AG" & Counter).Select
' ActiveWorkbook.Worksheets("Load Details")
Detail.Sort.SortFields.Clear
Range("A1:AG" & Counter).Sort Key1:=Columns("C"), Order1:=xlAscending, Key2:=Columns("A"), Order2:=xlDescending, Key3:=Columns("B"), Order3:=xlDescending, Header:=xlYes
' Color each load the same alternating blue and white to make the document more readable
Range("A2:AG2").Interior.Color = 14599344
For Stps = 2 To Counter
If Cells(Stps, 1).Value = Cells(Stps + 1, 1).Value Then
Range("A" & Stps + 1, "AG" & Stps + 1).Interior.Color = Cells(Stps, 1).Interior.Color
Else
If Cells(Stps, 1).Interior.Color = 14599344 Then
Range("A" & Stps + 1, "AG" & Stps + 1).Interior.Color = 16777215
Else
Range("A" & Stps + 1, "AG" & Stps + 1).Interior.Color = 14599344
End If
End If
Next Stps
Range("A" & Counter + 1, "AG" & Counter + 1).Interior.Color = 16777215
' Get rid of the LHFLAT and UNLDCH Columns
Columns("T:T").Delete Shift:=xlToLeft
Columns("Q:Q").Delete Shift:=xlToLeft
' Sum the important columns, and format as Currency and produce grand total
Range("L" & Counter + 2).Value = Format(WorksheetFunction.Sum(Range("L2", "L" & Counter)), "Currency")
Range("M" & Counter + 2).Value = Format(WorksheetFunction.Sum(Range("M2", "M" & Counter)), "Currency")
Range("N" & Counter + 2).Value = Format(WorksheetFunction.Sum(Range("N2", "N" & Counter)), "Currency")
Range("O" & Counter + 2).Value = Format(WorksheetFunction.Sum(Range("O2", "O" & Counter)), "Currency")
Range("P" & Counter + 2).Value = Format(WorksheetFunction.Sum(Range("P2", "P" & Counter)), "Currency")
Range("Q" & Counter + 2).Value = Format(WorksheetFunction.Sum(Range("Q2", "Q" & Counter)), "Currency")
Range("R" & Counter + 2).Value = Format(WorksheetFunction.Sum(Range("R2", "R" & Counter)), "Currency")
Range("S" & Counter + 2).Value = Format(WorksheetFunction.Sum(Range("S2", "S" & Counter)), "Currency")
Range("T" & Counter + 2).Value = Format(WorksheetFunction.Sum(Range("T2", "T" & Counter)), "Currency")
Range("K" & Counter + 6).Value = "Grand Total"
Range("K" & Counter + 6).Font.Bold = True
Range("L" & Counter + 6).Value = Format(WorksheetFunction.Sum(Range("L" & Counter + 2, "T" & Counter + 2)), "Currency")
With Range("L" & Counter + 6).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
' Freeze Pane the top Row
Rows("2:2").Select
ActiveWindow.FreezePanes = True
' Formatting to make it look nicer
Columns("G:G").ColumnWidth = 25
Columns("L:L").ColumnWidth = 25
Columns("M:M").ColumnWidth = 25
Columns("N:N").ColumnWidth = 25
Columns("O:O").ColumnWidth = 25
Columns("P:P").ColumnWidth = 25
Columns("Q:Q").ColumnWidth = 25
Columns("R:R").ColumnWidth = 25
Columns("S:S").ColumnWidth = 25
Columns("T:T").ColumnWidth = 25
Columns("U:U").ColumnWidth = 25
Columns("X:X").ColumnWidth = 25
Columns("AA:AA").ColumnWidth = 25
Columns("AD:AD").ColumnWidth = 25
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
' Turn on Screen updating to see new Invoice generated
Application.ScreenUpdating = True
' Create and format new worksheet for the invoice
Set Inv = Sheets.Add
Inv.Name = "Invoice"
Columns("A:A").ColumnWidth = 14.57
Columns("C:C").ColumnWidth = 22.14
Columns("E:E").ColumnWidth = 14.57
Columns("G:G").ColumnWidth = 14.57
'Address and Headings
Range("A1") = "Advantage Tank Lines"
Range("A2") = "P.O. Box 35519"
Range("A3") = "Canton, OH 44735-5519"
Range("A5") = "Bill to:"
Range("A9") = "Remit to:"
Range("C5") = "Country Fair"
Range("C6") = "2251 East 30th St."
Range("C7") = "Erie, PA 16510"
Range("C9") = "Advantage Tank Lines"
Range("C10") = "P.O. Box 35519"
Range("C11") = "Canton, OH 44735-5519"
Range("F1") = " ORIGINAL INVOICE"
Range("G3").Formula = "=TODAY()"
Range("G3").Copy
Range("G3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("G6") = "Invoice"
Range("G6").HorizontalAlignment = xlCenter
Range("G6").Font.Bold = True
Range("G7") = "Number"
Range("G7").HorizontalAlignment = xlCenter
Range("G7").Font.Bold = True
With Range("G8").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Range("G8").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Range("G8").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Range("G8").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
Range("B15") = "Monthly billing for Accessorial Charges and Fuel Surcharge"
Range("B15").Font.Bold = True
Range("B15").Font.Italic = True
Range("B15").Font.Underline = xlUnderlineStyleSingle
With Range("B15").Font
.Name = "Arial"
.Size = 14
End With
Range("C19") = "Description"
Range("E19") = "Amount"
With Range("C19:E19").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Range("C20") = "Stop-Off Unloading"
Range("E20") = Detail.Range("T" & Counter + 2)
Range("C21") = "Pump Out"
Range("E21") = Detail.Range("Q" & Counter + 2) + Detail.Range("R" & Counter + 2)
Range("C22") = "Holiday Surcharge"
Range("E22") = Detail.Range("N" & Counter + 2)
Range("C23") = "Extra Mileage Charge"
Range("E23") = Detail.Range("M" & Counter + 2)
Range("C24") = "Minimums"
Range("E24") = Detail.Range("O" & Counter + 2)
Range("C25") = "Fuel Surcharge"
Range("E25") = Detail.Range("L" & Counter + 2)
Range("C26") = "Unload Delays cars over fills"
Range("E26") = Detail.Range("S" & Counter + 2)
Range("C27") = "Loading Delays Rack Issues"
Range("E27") = Detail.Range("P" & Counter + 2)
Range("C29") = "Total Amount Due"
Range("E29") = Detail.Range("L" & Counter + 6)
Range("E20:E29").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
With Range("C29:E29").Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
Range("C29:E29").Font.Bold = True
Dim Strt As String
Dim Endate As String
Dim OrdNum As String
Strt = WorksheetFunction.Text(Detail.Range("C2"), "mmm dd, yyyy")
Endate = WorksheetFunction.Text(Detail.Range("C" & Counter), "mmm dd, yyyy")
Range("B17") = Strt & " through " & Endate
Range("B17").Font.Bold = True
With Range("B17").Font
.Name = "Arial"
.Size = 14
End With
OrdNum = WorksheetFunction.Text(Detail.Range("C" & Counter), "yyyymmdd")
Range("G8") = OrdNum
Range("G8").Font.Bold = True
Range("G8").HorizontalAlignment = xlCenter
With Range("G8").Font
.Name = "Arial"
.Size = 12
End With
' Subtotal all the order #'s (for Alex)
Detail.Sort.SortFields.Clear
Worksheets("Load Details").Range("A1:AE" & Counter).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 9, 10, 11, 12, 13, 14, 15 _
, 16, 17, 18, 19, 20), Replace:=True, PageBreaks:=False, SummaryBelowData:= _
True
Worksheets("Load Details").Outline.ShowLevels RowLevels:=2
' Reset the row counter
Worksheets("Load Details").Activate
Counter = Cells(Rows.Count, "A").End(xlUp).Row
Counter = Counter - 1
Worksheets("Load Details").Range("U4:AE" & Counter).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.FormulaR1C1 = "=R[-1]C"
Worksheets("Load Details").Range("C4:G" & Counter).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.FormulaR1C1 = "=R[-1]C"
Worksheets("Load Details").Columns("A:A").ColumnWidth = 50
Worksheets("Load Details").Columns("A:A").EntireColumn.AutoFit
Worksheets("Load Details").Cells.Select
Worksheets("Load Details").Cells.EntireRow.AutoFit
Worksheets("Load Details").Outline.ShowLevels RowLevels:=2
' Make Borders around all cells and delete zeros
Application.ScreenUpdating = False
With Worksheets("Load Details").Range("A2:AE" & Counter).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
For Each cell In Worksheets("Load Details").Range("U2:AE" & Counter)
If cell.Value = 0 Then
cell.ClearContents
End If
Next cell
Range("M2:T" & Counter).NumberFormat = "$#,##0.00"
Range("H" & Counter + 1 & ":K" & Counter + 1).ClearContents
Application.ScreenUpdating = True
Worksheets("Invoice").Activate


End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
You usually get that error when there is a line of what Excel considers to be executable code, but it's not in a sub, it's just sitting there.

Example:
Code: 
Range("A1").value = 1
Sub Test()
'code here.
End Sub
The first line is not between sub and end sub.
 
Upvote 0
skywriter said:
You usually get that error when there is a line of what Excel considers to be executable code, but it's not in a sub, it's just sitting there.

Example:
Code: 
Range("A1").value = 1
Sub Test()
'code here.
End Sub
The first line is not between sub and end sub.
Click to expand...


You were correct - thanks a lot. For some reason, when I copied the code it removed the "Sub" text. It also appears I need to make sure the cursor is at the very beginning of the code. In any case, I got it to work. Thanks again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Copy Cell as Hyperlink to another cell
Replies
1
Views
227
eli_m
E
C
  • Solved
VBA - Checking for Duplicates
Replies
2
Views
791
cwhaley1
C
J
  • Solved
Run time error for Worksheet Change in VBA
2
Replies
17
Views
395
jtemp57
J
J
  • Solved
populating dates of the current month with a formula
Replies
6
Views
348
jbrown021286
J
P
  • Solved
VBA Code to move entire row of data based on multiple number criteria
2 3 4
Replies
31
Views
841
mumps
mumps

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top