Try this:

Sub Date_File_Sheet_Name()
'
' Date_File_Sheet_Name Routine
' Enters current  date, path, Workbook name and current sheet  name in the active
cell.
' Moves one cell  down, and enters the current sheet name.
'
ActiveCell.FormulaR1C1 = "=CONCATENATE(TEXT(NOW(),""dd mmmm yyyy""), "", "",
CELL(""filename""))"
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=Right(R[-1]C,Len(R[-1]C)-Find(""]"",R[-1]C))"
End Sub