Time & Date Stamp 2 worksheets

[Guest]

I have a cell on 1 worksheet for time and date stamp along with other data.
I went to tools, options, calculations, and set it to manual. This puts the
latest date and time in the cell when saved. My issue is that my spreadsheet
has 2 tabs. My data on my second tab does not auto reference until I save
the document. Can I have one worksheet update automatically and the other
update manually?

 

[Gord Dibben]

Dot

I saw your earlier thread about using manual calculation and the TODAY()
function and understand what you want to achieve.

This is not a good idea as eventually the sheet will calculate and your TODAY()
will update.

You would be better off to use some before save code that inserts a static date
only when you save the workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
ActiveSheet.Range("A1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

Right-click on the Excel Icon left of "File" on the main menu and select "View
Code"

Paste the above code into that module.

Save your workbook and the date and time will be inserted to A1 of the active
sheet.

You can edit the A1 to any cell you wish.

You could also change activesheet to Sheets("Sheet1") or any sheetname you wish.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks, Exactly what I was trying to do. Very Helpful