Thank you so much! You were right, my file was called Master.xlsm. I wasn't thinking, but I realized it as soon as I read what you wrote. I tried to insert my code right before the "save" function, but could not get it to work. I am sure my code is a little sloppy, but it works. Here is what I am using as the code:
Sub FleetMatics()
'
' FleetMatics Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Cells.Replace What:="10291 MIAMISBURG-SPRINGBORO RD", Replacement:="10291 MIAMISBURG-SPRINGBORO PIKE", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="RYAN A", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="MIKE T", Replacement:="10", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="MICHAEL W", Replacement:="11", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="RANDY H", Replacement:="12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="ROB L", Replacement:="13", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="DEREK B", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="JOSH I", Replacement:="15", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="BRIAN J", Replacement:="16", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="NICK G", Replacement:="17", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="DONALD D", Replacement:="2", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="CHRIS D", Replacement:="3", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="DYLAN M", Replacement:="14", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="FLOYD D", Replacement:="5", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="VONG V", Replacement:="6", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="LEE K", Replacement:="7", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="MIKE K", Replacement:="8", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="LAWRENCE S", Replacement:="9", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.SmallScroll Down:=-6
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("H:K").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("G:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("C:C").Select
Selection.Copy
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("R:R").Select
Application.CutCopyMode = False
Selection.Cut
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Columns("T:T").Select
Selection.Cut
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
Columns("N:Q").Select
Selection.Delete Shift:=xlToLeft
Columns("O:O").Select
Selection.Cut
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Columns("P:U").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.CurrentRegion.Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "99999"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Fuel Card ID"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Location Name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Street"
Range("E1").Select
ActiveCell.FormulaR1C1 = "City"
Range("F1").Select
ActiveCell.FormulaR1C1 = "State"
Range("F1").Select
ActiveCell.FormulaR1C1 = "State/Region"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Postal Code"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Country"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Place ID"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Time"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Gallons Purchased"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Price Per Gallon"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Total Fuel Cost"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Product Description"
Range("A1").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Worksheets("Master Worksheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Master Worksheet").Sort.SortFields.Add Key:=Range( _
"A2:A184"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Master Worksheet").Sort
.SetRange Range("A1:O184")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Dim NewLastrow As Long
Lastrow = Cells(Rows.Count, "M").End(xlUp).Row
For i = Lastrow To 1 Step -1
If Cells(i, 12).Value = "" Then
Rows(i).Delete
End If
If IsNumeric(Cells(i, 1).Value) = "False" Then
Rows(i).Delete
End If
Cells(i, 13).Value = Format(Cells(i, 13).Value, "#.00")
If IsNumeric(Cells(i, 1).Value) = "True" Then
Range("N1:N" & Lastrow).Formula = "=L1*M1"
End If
Next
Range("A1").Select
ActiveCell.FormulaR1C1 = "Vehicle External ID"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Total Fuel Cost"
NewLastrow = Cells(Rows.Count, "N").End(xlUp).Row
For i = NewLastrow To 1 Step -1
Cells(i, 14).Value = Format(Cells(i, 14).Value, "#.00")
If Cells(i, 1).Value = "" Then
Rows(i).Delete
End If
Next
Columns("N:N").Select
Selection.NumberFormat = "0.00"
End Sub
|