Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jan 2010
    Location
    Sacramento, CA
    Posts
    7
    Thanks
    3
    Thanked 1 Time in 1 Post

    How to copy a cell's data to another cell on a time change? (2010)

    I have a need to copy a cell's value that was entered/calculated yesterday to another a cell when I first open the spreadsheet today. Hopefully I can make this clear using an example. Let's say A5 was updated yesterday when I manually refreshed a web connection. When I open the spreadsheet today, I want to copy A5 to B5. Then I will manually refresh the web connection to update A5. So now A5 has today's data and B5 has yesterday's. One condition must be that A5 is copied to B5 only the first time I open the spreadsheet that day. I obvioously don't want to copy it when I open the spreadsheet multiple times in a day.

    For the life of me I can't figure out how to set up this up. Any ideas would be appreciated. Thank you.

    P.S. A variation of this would be nice too if I could control the trigger time. For example, A5 would be copied after an hour change, i.e. copy A5 if opened after 6:00pm.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Art,

    Have your macro check the current date against the LastUpdated date for the workbook and then proceed if they are different.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jan 2010
    Location
    Sacramento, CA
    Posts
    7
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hi RetiredGeek,

    I'm not using any macros (I'm not sure I know how.) I was thinking the solution would be some sort of logic formula, IF(), AND(), etc. Is that not possible?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I was thinking the solution would be some sort of logic formula, IF(), AND(), etc. Is that not possible?
    I don't see how, unless you plan on keeping both values somewhere in the worksheet for the formula to choose. Why don't you just manually copy and paste-special value right before you manually refresh...

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    artg (2011-10-08)

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ok, here's the code to do the job. I've also attached a workbook with the code in place. The workbook was saved today 9/27 so if you open it today nothing will happen unless you change your system date to tomorrow first. If you do that the word "Test" in A5 will be copied to B5. Note since you are not familiar with macros to get this code into your current workbook do the following.
    1. Copy the code in the box below.
    2. Open your workbook
    3. Press Ctrl+F11 - this opens the VBA editor.
    4. Click Insert
    5. Click Module
    6. Click in the right hand window and paste {Ctrl+V} the code.
    7. Close the VBA editor by clicking the Red X
    8. Save your workbook {this will also save the macro}.
    9. You're ready to roll.

    Code:
    Option Explicit
    
    Sub Auto_Open()
    
       Dim zLastSaveDate As String
       Dim zCurDate      As String
       
       zCurDate = Format(Now, "mm/dd/yy")
       zLastSaveDate = Format(ActiveWorkbook.BuiltinDocumentProperties.Item("Last Save Time"), "mm/dd/yy")
       If zCurDate <> zLastSaveDate Then
         [B5].Value = [A5].Value
       End If
         
    End Sub
    Good Luck!
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    New Lounger
    Join Date
    Jan 2010
    Location
    Sacramento, CA
    Posts
    7
    Thanks
    3
    Thanked 1 Time in 1 Post
    Quote Originally Posted by sdckapr View Post
    Why don't you just manually copy and paste-special value right before you manually refresh...

    Steve
    Thanks for the easy solution. Guess I was trying to use a sledge hammer to hit a nail.

  8. #7
    New Lounger
    Join Date
    Jan 2010
    Location
    Sacramento, CA
    Posts
    7
    Thanks
    3
    Thanked 1 Time in 1 Post
    RetiredGeek, thank you much for the instructions and code. Will add that to the toolbox.

Posting Permissions

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