Save cell with "Today" function as a static date in new file

[MrBart67]

Hello,

I need help with saving an Invoice Workbook which contains the "Today()" function using a Macro.

The Active Workbook (Invoice) has the "Today()" function in Cell B14. Auto Calculate is active so whenever I open the Workbook todays date is displayed. So far so good
I have 3 Macro's that can save the Active Workbook to different file types according to the 'Status' of the Invoice.
2 of them save the Workbook to PDF and work fine. The third Macro saves the Workbook As a Copy to a different folder.

Sub SaveInvoiceOpen()
Dim NewFN As Variant
' Copy Invoice to a New Workbook
ActiveWorkbook.SaveCopyAs Filename:="X:\RON\Facturen\Open\Factuur-" & Range("B15") & Range("C15").Text & ".xlsm"
Range("C15").Value = Range("C15").Value + 1
Range("A7:A12").ClearContents
Range("A18:F32").ClearContents
Range("B38:B40").ClearContents

End Sub

Question: Is it possible to change Cell B14 to show the date as text upon saving the Workbook to a new file? But Cell B14 has to remain the "Today()" function in the Active Workbook.

What I want in the Saved Copy is to remain the date to the date the file was created.

Thanks in advance and please feel free to reply with any question?

 

[Joe4]

Welcome to the Board!

I think you really mean you want the date in cell B14 to be a hard-coded value that doesn't change, and not a formula, rather than "text" (which would then NOT be a date).
If so, then does this do what you want?

Sub SaveInvoiceOpen()
Dim NewFN As Variant
' Copy Invoice to a New Workbook
ActiveWorkbook.SaveCopyAs Filename:="X:\RON\Facturen\Open\Factuur-" & Range("B15") & Range("C15").Text & ".xlsm"
Range("B14").Value = Range("B14").Value
ActiveWorkbook.Save
Range("C15").Value = Range("C15").Value + 1
Range("A7:A12").ClearContents
Range("A18:F32").ClearContents
Range("B38:B40").ClearContents

End Sub

(note the new lines of code added in red).

 

[MrBart67]

Hi Joe4,

Thanks for the quick reply.
Your added code does the opposite from what I want. ?
The version that is saved as a copy should have te Value as date and the active workbook should remain with the Today function so I can fill in the next invoice.
The saved copy has to have the value date so it will not change if I open this file for say 2 weeks.

Thanks in advance.

 

[Joe4]

I apologize. In my haste, I saw "SaveAs" instead of "SaveCopyAs".
I think if we move things around, and make a small change, it should work.

Sub SaveInvoiceOpen()
Dim NewFN As Variant
' Copy Invoice to a New Workbook
Range("B14").Value = Range("B14").Value
ActiveWorkbook.SaveCopyAs Filename:="X:\RON\Facturen\Open\Factuur-" & Range("B15") & Range("C15").Text & ".xlsm"
Range("B14").Formula = "=TODAY()"
Range("C15").Value = Range("C15").Value + 1
Range("A7:A12").ClearContents
Range("A18:F32").ClearContents
Range("B38:B40").ClearContents

End Sub

This sets it to be hard-coded before saving the copy, then sets it back afterwards.

 

[MrBart67]

Apologies accepted off course

This works !!!! Thank you very much. ?

Have A Nice Day and Stay Healthy.

 

[Joe4]

You are welcome.
Glad it works for you!