![]() |
|
#1
|
|||
|
|||
![]()
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 |
![]() |
Thread Tools | |
Display Modes | |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
PP and Excel integration | ShayA | PowerPoint | 0 | 07-01-2016 01:37 AM |
Onenote 2016 integration with Outlook 2007 | killernils | OneNote | 0 | 01-28-2016 03:03 AM |
Powerpoint Integration | Trek-Fan | PowerPoint | 0 | 07-24-2014 07:24 AM |
Lync / Outlook integration | kneghx | Misc | 0 | 04-18-2014 07:35 AM |
![]() |
stephen_pen | Mail Merge | 1 | 09-26-2011 04:53 AM |