Using Excel 2003. This is my first post although I view and get others' knowledge sometimes. I am tring to work on cleaning my code. Please help.
Thanks..
[VBA]
Sub Update()
Dim LFile As String
Application.ScreenUpdating = False
Dim Pth As String
Pth = "C:\CURRENT\"
LFile = LatestFile(Pth)
Workbooks.Open Pth & LFile
Range("A1:H1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ThisWorkbook.Activate
Range("K1").Select
ActiveSheet.Paste
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="NEWEVT", RefersToR1C1:= _
"='EVENT ANALYSIS'!R2C2:R71C2"
Range("L29").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="OLDEVT", RefersToR1C1:= _
"='EVENT ANALYSIS'!R29C12:R71C12"
Range("I29").Select
Range("A1:H1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("K1:R1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("P2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("I2").Select
ActiveSheet.Paste
Columns("J:J").Select
Application.CutCopyMode = False
Range("J2").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-1]"
Columns("J:J").Select
Selection.NumberFormat = "General"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J71"), Type:=xlFillDefault
Range("J2:J71").Select
Range("J59").Select
Columns("I:I").Select
Selection.EntireColumn.Hidden = True
Columns("K:R").Select
Selection.Delete Shift:=xlToLeft
Range("L9").Select
Cells.Select
Range("A2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("G43").Select
Range("D2").Select
Range("J1").Select
ActiveCell.FormulaR1C1 = "DIFF FROM LAST WEEK"
Range("J1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("K10").Select
End Sub
Function LatestFile(Pth As String)
Dim fdate, tmp, fname As String, LastFile As String
tmp = 0
fname = Dir(Pth & "EVENT ANALYSIS REPORT" & strdate & "*.xls")
Do
On Error Resume Next
fdate = Split(fname, "of ")(1)
fdate = CDate(Split(fdate, ".")(0))
If fdate > tmp Then
tmp = fdate
LastFile = fname
End If
fname = Dir
Loop Until fname = ""
LatestFile = LastFile
End Function
[/VBA]