+ Reply to Thread
Results 1 to 20 of 20

Inserting last save date in cell

  1. #1
    Forum Contributor
    Join Date
    06-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    152

    Inserting last save date in cell

    Hi,

    Can anyone advise how I can have the date of the last save automatically populate in a cell? I can do it in the footer but need to actually have it in the body of the spreadsheet to advise people of the last time the spreadsheet was updated.

    Thanks,
    Last edited by greyscale; 02-27-2011 at 08:28 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inserting last save date in cell

    This would require VBA. Is that OK?

    If so, you could add this capability as a new function to your workbook.

    How to install the User Defined Function:

    1. Open up your workbook
    2. Get into VB Editor (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste in your code
    Please Login or Register  to view this content.
    5. Get out of VBA (Press Alt+Q)
    6. Save as a macro-enabled workbook

    The function is installed and ready to use.

    In a cell enter the formula:

    =LASTSAVE()
    ...and format the cell to display as date/time.

    This function will only display an answer on workbooks that have been saved at least once.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    06-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: Inserting last save date in cell

    Thanks for your reply, JBeaucaire. I have never used VBA before. I have followed your instructions (very clear, thanks!) but, assuming that step 6 refers to a normal save procees, I only seem to have an option for a 2007 macro enabled workbook, and I am using 2003. Do you know what I might be missing or is not possible in my version?

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Inserting last save date in cell

    In 2003 there is no difference between how to save a workbook with or without macros, so just save it.

    (In >=2007, if you want to preserve macros you must save in .xlsm format.)
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inserting last save date in cell

    How did it work for you?

    If you're having trouble, upload your workbook (or any sample workbook) where you've installed that UDF and then tried to use the new function in a cell somewhere. Let us look at what you did.

    Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

  6. #6
    Forum Contributor
    Join Date
    06-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: Inserting last save date in cell

    Thanks for following up JBeaucaire. Not sure what I am doing wrong. When I press Alt+F11 I can see the code but when I enter it in the cell I get #NAME?. I have attached a sample doc but I have had to remove all other cells for security reasons. Thanks.
    Attached Files Attached Files

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Thumbs up Re: Inserting last save date in cell

    I do not see the code in this workbook. When you press ALT-F11 and you say you see the code, where do you see it? In the left pane you should see a folder structurefor your file:

    VBAProject (test savedate.xls)
    Microsoft Excel Objects
    Sheet1 (2011)
    ThisWorkbook
    You need to right click on the line for this file that says VBAProject and then select Insert > Module. Then you will see a new entry
    Modules
    Module1
    Double click on Module1. On the right side you will now see the pane for the code for this module. That's where your code should go.

    I am guessing that your code exists but is associated with a different workbook.

  8. #8
    Forum Contributor
    Join Date
    06-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: Inserting last save date in cell

    You were absolutley right! It was associated with a different workbook. I clicked on the file name before adding the module and now it is working perfectly. Thanks to everyone for your patience and help

  9. #9
    Registered User
    Join Date
    03-02-2012
    Location
    Montréal
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Inserting last save date in cell

    Thanks, this was perfect and very helpful.

    I use a french version of Excel so I wasn’t sure it would take, but it did.

  10. #10
    Registered User
    Join Date
    07-07-2012
    Location
    Demokratik Peoples Republik of Kommiefornia
    MS-Off Ver
    Excel 2007/10
    Posts
    7

    Re: Inserting last save date in cell

    Over a year later I find this thread and it did the trick, thank you very much!!!!

  11. #11
    Registered User
    Join Date
    11-21-2012
    Location
    Gateshead
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Inserting last save date in cell

    Is it possible to modify this to work for individual worksheets rather than the whole workbook (i.e. it reflects which sheet has had a change made to it during the save?)
    Last edited by marklord1982; 11-21-2012 at 12:15 PM.

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inserting last save date in cell

    Quote Originally Posted by marklord1982 View Post
    Is it possible to achieve this for individual worksheets (i.e. it reflects which sheet has had a change made to it during the save?)
    As per Forum Rules, start a thread of your own and present your question fully. You can provide a link to this thread in yours if you really think it's relevant, I don't.

  13. #13
    Registered User
    Join Date
    12-03-2012
    Location
    Israel
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Inserting last save date in cell

    This tip helped me on excel 2010.
    very helpful thanks.

  14. #14
    Registered User
    Join Date
    04-25-2013
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Inserting last save date in cell

    Quote Originally Posted by JBeaucaire View Post
    This would require VBA. Is that OK?

    If so, you could add this capability as a new function to your workbook.

    How to install the User Defined Function:

    1. Open up your workbook
    2. Get into VB Editor (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste in your code
    Please Login or Register  to view this content.
    5. Get out of VBA (Press Alt+Q)
    6. Save as a macro-enabled workbook

    The function is installed and ready to use.

    In a cell enter the formula:

    =LASTSAVE()
    ...and format the cell to display as date/time.

    This function will only display an answer on workbooks that have been saved at least once.
    JBeaucaire,
    Feeling a bit lost here as I have the same issue. I did all the VBA script copy and paste and quit VBA editor. Now how on earth do I get the last updated "date and time stamp" on the sheet? I only need it to say so in a right footer. I have saved the file as a Macro enabled spreadsheet. I need this really fast Chief. Would appreciate all help.

  15. #15
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Inserting last save date in cell

    See post #12.

  16. #16
    Registered User
    Join Date
    06-12-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Inserting last save date in cell

    This was extrememly helpful. Thank you.

  17. #17
    Registered User
    Join Date
    10-04-2013
    Location
    Sacramento, CA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Inserting last save date in cell

    YESSSSSSS! Thank you. I've been struggling with this for a RIDICULOUS amount of time. Thank you!!

  18. #18
    Registered User
    Join Date
    11-27-2013
    Location
    Denver, Colorado USA
    MS-Off Ver
    Excel 1010
    Posts
    1

    Re: Inserting last save date in cell

    Thank you!!!

  19. #19
    Registered User
    Join Date
    01-28-2014
    Location
    Paris, France
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Inserting last save date in cell

    I just used this on an Excel 2010 worksheet and it worked just fine......

    simple procedure, just do exactly as stated above.

    Austin Powers: Ooo, Behave!

  20. #20
    Registered User
    Join Date
    11-18-2014
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    5

    Re: Inserting last save date in cell

    I am a starter of using VBA, it is very useful! Thx

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1