Copy to last row with unspecified column

[freaknyea]

I need to copy a formula down without specifying the columns that they will be copied down in. I'm trying to make this macro based on the cell that I have active at the time of initiating the macro.

You can see below where I'm stuck. Originally, I made the macro just from clicking through cells, but now I'm trying to unspecify everything.

Anyone able to help me out?

Sub DAYMONTHYEAR()
'
' DAYMONTHYEAR Macro
'

'

ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Insert

ActiveCell.Offset(0, 1).FormulaR1C1 = "Month"
ActiveCell.Offset(0, 2).FormulaR1C1 = "Day"
ActiveCell.Offset(0, 3).FormulaR1C1 = "Year"

ActiveCell.Offset(1, 1).FormulaR1C1 = "=MONTH(RC[-1])"
ActiveCell.Offset(1, 2).FormulaR1C1 = "=DAY(RC[-2])"
ActiveCell.Offset(1, 3).FormulaR1C1 = "=YEAR(RC[-3])"


ActiveCell.Offset(1, 1).Resize(1, 3).Select

' ***Stuck Here***
Selection.AutoFill Destination:=Range("B2:D45"), Type:=xlFillDefault
Range("B2:D45").Select
ActiveWindow.SmallScroll Down:=-45
Selection.NumberFormat = "General"
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
ActiveCell.FormulaR1C1 = "New Date"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]&""-""&RC[-2]&""-""&RC[-1]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E45"), Type:=xlFillDefault
Range("E2:E45").Select
ActiveWindow.SmallScroll Down:=-39
Range("F2").Select
End Sub

[mdmackillop]

I think a sample workbook is required.

[freaknyea]

Here's an example workbook.

Thanks.

[mdmackillop]

Sub WHATIWANTITTODO()
Dim r As Range
Set r = Cells.Find("Date")
r.Offset(, 1).Resize(, 4).EntireColumn.Insert
r.Offset(, 1).Resize(, 4) = Array("Month", "Day", "Year", "New Date")
Set r = Range(r.Offset(1), r.End(xlDown))
r.Offset(, 1).FormulaR1C1 = "=MONTH(RC[-1])"
r.Offset(, 2).FormulaR1C1 = "=DAY(RC[-2])"
r.Offset(, 3).FormulaR1C1 = "=YEAR(RC[-3])"
r.Offset(, 4).FormulaR1C1 = "=RC[-3]&""/""&RC[-2]&""/""&RC[-1]"
r.Offset(, 1).Resize(, 4).NumberFormat = "General"
End Sub