Backup copy in a different location

[lapta301]

I'm using the following workbook code to frequently save a timed and dated copy of the file in a backup location elsewhere before saving the file at it's normal location.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fn As String, ext As String, ipos As Long
ipos = InStr(Me.Name, ".")
fn = Left(Me.Name, ipos - 1)
ext = Right(Me.Name, Len(Me.Name) - ipos)

Application.StatusBar = " "

Me.SaveCopyAs "C:\Users\S\Documents\Backup\Excel" & fn & " " & Format(Now, "yyyy-mm-dd_hh_mm_ss") & "." & ext
Application.StatusBar = False
End Sub


However, in the workbook at E16 on a sheet that has the internal name of Sheet16 (as the sheet name is changed by me each month) I have a formula that inserts the current date and time.

What I would like to achieve somehow is that the cell value is saved not the formula but only on the backup copy.

I should mention that after I save the file I normally carry on working and may save a number of additional copies before I shut up shop for the day so it is not just a case of saving the backup copy after the working copy is saved

I hope that this makes sense

As always any help is much appreciated

 

[ScottR]

Slightly confused as to whether I've understood what you're asking as it seems to be (far) simpler than you've already done

eg

Dim mCell as Date
mCell=thisworkbook.sheets("Sheet16").range("E16")

Then use that value in the save copy as portion

 

[lapta301]

Hi Scott


Thank you for showing an interest.

Let me explain.

I can spend hours on this workbook and whenever I saved it I wanted a dated and timed backup copy saved in another location and the code that I have shown does that perfectly.

The only issue that I have had for a long time now is that if I go back to have a look at an older version which may be months, old in that particular cell it shows todays date rather than the date and time that it was saved.

Yes I know that I can identify the date from the file properties but it would be helpful if possible to have the formula in E16 replaced by the actual value instead of the formula.

 

[ScottR]

That makes much more sense to me, thanks. Procedure would be this.

In your before save event, overwrite the date time formula with a static value

(eg sheets("Sheet16").range("E16")=sheets("Sheet16").range("E16").value)

Then in an after save event, restore the dynamic formula.

Hope that helps