Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Location
    Peru
    Posts
    21
    Thanks
    0
    Thanked 1 Time in 1 Post

    changing =today() data to a static date

    This is not a question; I'm sharing something I have found that others may want to use. If anyone has comments or other ways to do it, feel free to contribute to the thread.

    I have been trying to write a macro on a worksheet that has the today function. I wanted it to save the workbook but keep the date as it was when saved, not updating the next time I open it. After looking online and getting a number of round about and complicated ideas, I checked on a simple idea and it has worked for me - and might help others.

    What I ended up doing was copying the cell with the today function.
    selecting the cell where I wanted the date info,
    and doing a paste special, selecting the values and number format option.
    The result is the date as a static date, not the calculation of the function!

    The pertinent code is:
    Range("B2").copy
    Range("B3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    Change B2 to the cell where today function is
    change B3 to the cell where you want the static date value

    BTW, I used this after savecopyas in the copy of a wkbk, and used B2 for both, thus copying the today and replacing it with the value. That gives me a copy with the date last saved.

  2. The Following User Says Thank You to sati For This Useful Post:

    Maudibe (2017-09-14)

  3. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,929
    Thanks
    4
    Thanked 295 Times in 267 Posts
    Or you could do it with one line of code
    Range("B2").value = Date
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,978
    Thanks
    156
    Thanked 769 Times in 701 Posts
    You probably discovered the code behind the code. Always nice to see another way of doing something.

    Thanks,
    Maud

Tags for this Thread

Posting Permissions

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