Absolute date/Saved at -date

  • Thread starter Thread starter Panu Kinnari
  • Start date Start date
P

Panu Kinnari

Hi,

Is it possible to get cell to aquire saved at -date as
it's content?

I have a packing list spreadsheet which I use weekly, but
to minimize changes I need to do each time I put TODAY()
function into date cell. It works fine for printing the
sheet. But I have to archive these files as well, so I
don't want for that date to change once I have saved it
into archive folder.

Thanks in advance.

Panu Kinnari
 
Hi Panu

there's basically only two ways of achieving this (AFAIK)
1) when you're ready to archive it, click on the cell with the date & copy
it, now stay on the cell and choose edit / paste special, click value &
click Ok ... this will change the formula into a value
2) use some code to do this for you ... you can record a macro to do this
(step 1) above & then run the macro every time you are ready to archive the
workbook (or have it run automatically when you save the workbook)

if the date cell is always the same cell - (set your security settings to
medium - tools / macro / security) -
then
1) click on the cell with the date in it
2) choose tools / macro / record new macro
3) give it a name (e.g. ChangeDate) and store it in "Personal Macro
Workbook"
4) click the OK button
5) do # 1 above - the copy / paste special bit
6) click on the stop recording button

now when you need to use it
1) click in the cell with the date function
2) choose tools / macro / macros from the menu & click on your macro -
choose RUN

Let us know how you go.

Cheers
JulieD
 
Hi Paul,

As Julie said, the copy/paste special/value will do what
you're after. If you use a new template each time, you'll
be ok, but if you save that file and reopen it, the
automatic date function will be gone. I ran into this a
few weeks back and took this approach.

I created a button and attached a macro that saved the
active sheet to a new book, did the copy/paste
special/value in that new book, saved and renamed the new
book to conform to my file structure.

You can also, before saving, select the entire "New Book",
copy/paste special/value from "A1" (assuming your "New
Book" starts in "A1". This will reduce the file
significantly if you have many formulas in it and also
will "Unlink" it to it's source.

HTH,

Don
 
Panu Kinnari said:
Hi,

Is it possible to get cell to aquire saved at -date as
it's content?

I have a packing list spreadsheet which I use weekly, but
to minimize changes I need to do each time I put TODAY()
function into date cell. It works fine for printing the
sheet. But I have to archive these files as well, so I
don't want for that date to change once I have saved it
into archive folder.

Thanks in advance.

Panu Kinnari
Click to expand...
The other two answers are complete, but I'll throw in my $.02 as well. We have a daily Order Recap sheet that this macro in the Workbook module:

Private Sub Workbook_Open()
If Sheets("master").Range("b3") = "" Then
Sheets("master").Range("b3") = Date
Else
End If
End Sub

So if the Date cell is blank, the current date is entered in, otherwise it is not changed. As I look at it now, I realize I should also include a workbook.SaveAs line in there too to insure the user doesn't accidently save over the master copy.

Marcotte
 
:

So if the Date cell is blank, the current date is entered in, otherwise it is not changed. As I look at it now, I realize I should also include a workbook.SaveAs line in there too to insure the user doesn't accidently save over the master copy.

Marcotte
Click to expand...

I went ahead and modified the code so that when the user opens the spreadsheet, it Saves As "[Todays date] Order Recap.xls". As long as they enable macros, you don't have to worry about them saving over the master sheet.

Private Sub Workbook_Open()
If Sheets("master").Range("b3") = "" Then
Sheets("master").Range("b3") = Date
Else
End If
Dim stdate As String
Dim i As Integer
Dim inDateLen As Integer
stdate = Date
inDateLen = Len(stdate)
For i = 1 To inDateLen Step 1
If Mid(stdate, i, 1) = "/" Then stdate = _
WorksheetFunction.Replace(stdate, i, 1, "-")
Next
Dim ThisPath As String
ThisPath = ThisWorkbook.Path
ActiveWorkbook.SaveAs Filename:= _
ThisPath & "\" & stdate & " Order Recap.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
 
Back
Top