Find last row of data and paste

[Jen_T]

I am very new at macros and had recorded one for a worksheet I use all the
time. I copied/pasted the macro into another workbook that I could use this
the macro for but there is less data and has the potential to grow. In the
macro I recorded I had copied a formula and then autofilled down to last row
of data. How do I edit the macro to find last row and copy the formula down.
I am ending up with unecessary rows of formulas where there is no data.

 

[Don Guillett]

As always, post YOUR code for comments and suggestions.

 

[Jen_T]

Sorry about that,, it is abit lengthy and I am sure can be cleaned up:
Cells.Select
Selection.Columns.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
Workbooks.Open Filename:= _
"J:\\Monthly Exports\\Jan09systemdataexport.XLS"
Windows("Nicole_Fill_Export_File.xls").Activate
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "New Create Date"
Range("A1").Select
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Range("B1").Select
ActiveCell.FormulaR1C1 = "Prog Mth"
Range("A1").Select
ActiveCell.FormulaR1C1 = "Prog Yr"
Range("D1").Select

' Update file name below................

Windows("Jan09systemdataexport.XLS").Activate
ActiveWindow.SmallScroll ToRight:=1
Windows("Nicole_Fill_Export_File.xls").Activate
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Record Location"
Columns("C:C").EntireColumn.AutoFit
Range("E1").Select
Windows("Jan09systemdataexport.XLS").Activate
Windows("Nicole_Fill_Export_File.xls").Activate
Windows("Jan09systemdataexport.XLS").Activate
ActiveWindow.LargeScroll ToRight:=1
Columns("G:G").Select
Selection.Copy
Windows("Nicole_Fill_Export_File.xls").Activate
Sheets("Lookup").Select
Range("E1").Select
ActiveSheet.Paste
Windows("Jan09systemdataexport.XLS").Activate
ActiveWindow.LargeScroll ToRight:=-1
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Nicole_Fill_Export_File.xls").Activate
Range("F1").Select
ActiveSheet.Paste
Columns("E:F").Select
Range("F1").Activate
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="date", RefersToR1C1:="=Lookup!C5:C6"
Range("E2").Select
Sheets("Clean Data").Select
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[4],date,2,FALSE)"
Selection.NumberFormat = "m/d/yyyy"
Selection.AutoFill Destination:=Range("E2:E2003")
Range("E2:E2003").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[3],Prog,2,FALSE)"
Selection.NumberFormat = "[$-409]mmm-yy;@"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B2003")
Range("B2:B2003").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=YEAR(RC[1])"
Selection.AutoFill Destination:=Range("A2:A2003")
Range("A2:A2003").Select
Range("A2").Select
ActiveWindow.SmallScroll ToRight:=11
Range("Q1").Select
Selection.End(xlToLeft).Select
Range("Q1:BP1").Select
Selection.Replace What:="State (", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("CK1:CN1").Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.SmallScroll ToRight:=-5
Range("A1").Select
End Sub