Macro to copy cell and paste till last data in another column

[dingdang]

Hi,


I have recorded macro wherein I want to copy cell C2 till last row of column B. in cell C2 contain " report dt 14-feb-2013 ( todays date) but after copy cell from C2 till last row, year is changing as 2013..2014...2015 so on..


Pls help


code is :


VB:

ActiveCell.FormulaR1C1 = _

"=""Report dt""&TEXT(R[-1]C,""DD-MMM-YYYY"")"

Range("C2").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

lastrow = Range("B65536").End(xlUp).Row

Selection.AutoFill Destination:=Range("C2:C" & lastrow)

 

[Marc L]

Hi !   Try this :

[pre]

    ActiveCell.FormulaR1C1 = "=""Report dt ""&TEXT(R[-1]C,""DD-MMM-YYYY"")"
[C2] = [C2].Text
[C2].Copy Range([C3], Cells(Rows.Count, 2).End(xlUp).Offset(, 1))
 
Edit :

All you need in one line :

    Range([C2], Cells(Rows.Count, 2).End(xlUp).Offset(, 1)) = "Report dt " & Format([C1], "dd-mmm-yyyy")

[/pre]