Date and Timestamp - not reflecting individual worksheet times.

Hi, all.

I have some code that I've inserted to create individual timestamps to show when the Worksheet was last updated. However, it seems to be copying the time across each sheet, which is not what I want.

I first created a TimeStamp name in name Manager. The refer code was:

=TEXT(TODAY(),"d-mmmm-yyyy-") & ""& TEXT(NOW(),"h:mm AM/PM"

The macro code is:

Sub SaveAndTimeStamp()
'
' SaveAndTimeStamp Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Range("E3:F3").Select
ActiveCell.FormulaR1C1 = "=DateStamp"
Range("E4").Select
ActiveWorkbook.Save
End Sub


It does work, but unfortunately, it repeats the saved time across each worksheet.

Can someone please point me in the right direction.

Many thanks in advance,

Jaime.

it is working fine for me. If you have a specific sheet you want it to be done in and you sometimes have a diffferent sheet open when it runs then you can't use select as .select only works on the active sheet I believe.

Hi, Danny

Thanks for the response. So do I need to add any extra code ?

Thanks,

Jaime.

Hi, Danny

Yes. It is in the same cell across all worksheets.

Thanks,

Jaime.

Jaime,

I have just changed the above post. See if that does what you want and let me know.

Danny

Hi, Danny

I updated the code and I still have the same issue. It's like it's reading the same time update.

Thanks for the response,

Jaime.

Sorry, I misunderstood what you were asking. Give me 5 minutes and I will give you an answer. I think I understand now. Everytime you run it you are updating the formula which is in each sheet and therefore reflecting same date and time.

This should do what you want and put an actual value in rather than the formula.

Please Login or Register  to view this content.

Hi, Danny

I'm wondering if the issue has anything to do with have each sheet duplicated and renamed? What I have is a database of Contractors. What I need is a way to time and date stamp each individual sheet when it was updated and saved. I've just tried your code(and thank you so much for your efforts) but I still get the same time.

Maybe I should deleted the name in the name manager and retry again.

Would that be better?

Thanks again for your help,

Jaime.

Sorry, are all of these sheets in the same workbook? Can you please attach a desensitized version of your spreadsheet that I can look at and get a better idea of what you want to happen.

The last bit of code I provided will put it into whatever the active sheet is when you run the code. It will display different times on each sheet when you run it.

I am confused and not sure what you would like.

Hi, Danny

Yes, all these sheets are in the same workbook. Each sheet will include a contractor record. At the moment nothing has been added.

Thanks,

Jaime.

So if you want it to trigger whenever the workbook is saved, it will be the same date for all of them anyway? If you want a way to have a different time on each then perhaps you could have a button to press on each page? That will only put a time on that page?

Alternatively you could run it as

Private Sub Worksheet_Deactivate

rather than just as a sub. This would run it everytime you click off the sheet?

Please Login or Register  to view this content.

You would have to put this code in VBA for each worksheet of the workbook.

Hi, Danny

What I'll do is send you what I have when I get home tonight.

Again, thanks so much for helping me with this. Much appreciated.

Thanks,

Jaime.

Hi, Danny

What I'll do is send you what I have when I get home tonight.

Again, thanks so much for helping me with this. Much appreciated.

Thanks,

Jaime.

I won't be around now for 4 or 5 days. Hopefully somebody else can take a look once you've attached the spreadsheet.

Will the worksheet_deactivate sub not do what you need?

Hi, Danny

I found this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("A4").Value = Date
Sheets("Sheet1").Range("B4").Value = Time
End Sub

It works perfectly on saving. But it is only on one sheet. How can I deploy this so that all my sheets will work the same way?

Would this require each sheet to be referenced?

Thanks in advance, Jaime.

Hi, there.

I'm great with formulas but an absolute novice with VBA. How would the full code look if I added your event?

Thanks in advance,

Jaime.

Hi, there

I've just tested it on a blank worksheet and it works brilliantly. It doesn't seem to work on the database I'm working on. I have created the module and dropped your code in (altered the cell locations) but no go after changes and a save.

Each worksheet has a customer name on the tab. is that what's tripping it up?

Thanks again for your help.

Jaime.

Hi, there

I sorted it out. That's exactly what I'd missed. Now it works fine. Everything I alter and save on, it reflects the date and time change. I have also altered the cell location so it places the timestamp exactly where I want it.

Thank you so much for your help. I'm very grateful for your assistance in this matter.

Have a great day, and thanks again,

Jaime.