Date issue with macro

Hello again my friends

Hope all are well.
I was wondering if someone could assist me with. I have a speadsheet which has a macro button assigned, where once clicked it will save that spreadsheet under the date listed in a cell (depicted as NOW()). The sheet saves under the current date at the time of saving (great...) but when I open the sheet, the date is showing as the current day (due to the NOW () reference attached). I was wondering is there of manipulating my code so that the date within a saved spreadsheet will remain as it was the day it was saved? here is the code attached:

Sub Daily_Save()
'
'Macro created by Ivor 29/06/10
'Designed to save the file daily, under the current date automatically

Dim dailytaskfile As String
    Dim dailytaskpath As String
    Dim activeextract As String
    'activeextract = ActiveWorkbook
    ActiveWorkbook.Save
    dailytaskpath = Range("F3").Value
    dailytaskfile = Range("G3").Value
        
    Application.StatusBar = "Saving file ..."
    ActiveWorkbook.Save
'Reconciliation directory
    
    'ActiveWorkbook.ReadOnlyRecommended = False
    'ActiveWorkbook.WritePassword = ""
    
    ActiveWorkbook.SaveAs FileName:= _
        dailytaskpath & dailytaskfile, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Thanks guys

Ivor

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

The date_time in cell G3 should remain the same when you open the workbook saved using the macro and will only change if you run the macro again. It sounds like you are reopening the latest file (saved in your specified location) and running the macro from this file.

With, for example:

dailytaskpath = "C:\"
dailytaskfile = Format(Date, "ddmmyyyy") & "_" & Format(Time, "hhmmss")

How about something like this, using the workbook name to define the date and time stamp in cell A1 when the workbook is opened:

Private Sub Workbook_Open()

Dim date_time As String
Dim format_date_time As Date

date_time = Application.Substitute(ThisWorkbook.Name, ".xls", "")

format_date_time = Left(date_time, 2) & "/" & _
Mid(date_time, 3, 2) & "/" & Mid(date_time, 5, 4) & " " _
& Mid(date_time, 10, 2) & ":" & _
Mid(date_time, 12, 2) & ":" & Mid(date_time, 14, 2)

With Sheet1.Range("A1")
.Value = format_date_time
.NumberFormat = "dd/mm/yyyy hh:mm:ss"
End With

End Sub

Copy and paste the code into the ThisWorkBook module in the Visual Basic Editor.

I've taken another look at your code above and something like this should do it:

Sub Daily_Save()

Dim dailytaskfile As String
Dim dailytaskpath As String
Dim datetime As Date

dailytaskpath = Range("F3").Value
dailytaskfile = Range("G3").Value
datetime = Date & " " & Time
With Sheet1.Range("A1")
.Value = datetime
.NumberFormat = "dd/mm/yyyy hh:mm"
End With

ActiveWorkbook.SaveAs Filename:= _
dailytaskpath & dailytaskfile & "_" & _
Format(Date, "ddmmyyyy") & Format(Time, "hhmm"), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

No need for the workbook open event.