+ Reply to Thread
Results 1 to 6 of 6

Take a snapshot of a cell every Friday and store it in column

  1. #1
    Registered User
    Join Date
    02-04-2021
    Location
    Tricity
    MS-Off Ver
    Excel 2008
    Posts
    6

    Take a snapshot of a cell every Friday and store it in column

    Hi all,

    looked thru the forum, but can't seem to find an answer to my issue. I am looking for a way (can be a formula or macro) to take a snapshot of a specific cell (let's say B3) every Friday on a particular time & store it in an array - (column O), so I can later on see what the B3 value was in previous weeks. Attaching screenshot for reference, hope it's clear. The value in B3 will always be a simple value - a singular letter - G or A or R.

    Capture.JPG

    Would appreciate all the help :-)
    Last edited by MikeBlazej; 02-17-2021 at 08:21 AM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Take a snapshot of a cell every Friday and store it in column

    Possibly in the worksheet_calculate event, or possible, worksheet_change event?
    The code sets sunday the 1st day of week. The code will only continue with the action if the day of the week is 6.
    Please Login or Register  to view this content.
    The code would go in the worksheet module, see pic.

    Right Click select view code.jpg
    Last edited by davesexcel; 02-17-2021 at 08:49 AM.

  3. #3
    Registered User
    Join Date
    02-04-2021
    Location
    Tricity
    MS-Off Ver
    Excel 2008
    Posts
    6

    Re: Take a snapshot of a cell every Friday and store it in column

    Hi there. Thanks for the response - could you explain how to set it up for specific date/time & test it out?

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Take a snapshot of a cell every Friday and store it in column

    Ah yes the Time of day.

    I can't post the code, the forum website does not like the greater than or less than characters, I will have to attach a txt file.

    Here's a pic as well.

    2021-02-17_7-18-18.jpg

    The idea is to somehow trigger the code, I was thinking worksheet_calculate or Worksheet_SelectionChange possibly Workbook_BeforeClose, but that would be in the workbook module.

    At any rate, the code just won't run by itself unless it is triggered.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-04-2021
    Location
    Tricity
    MS-Off Ver
    Excel 2008
    Posts
    6

    Re: Take a snapshot of a cell every Friday and store it in column

    I think I understand where you're coming from. The only thing I am not sure about is triggering - in other words - is there a way to trigger it every Fri at, let's say, 12:00 ?

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Take a snapshot of a cell every Friday and store it in column

    Try Windows Task Scheduler to open the workbook

    https://www.thespreadsheetguru.com/b...a-macros-daily

    and code to run when open workbook
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 24
    Last Post: 04-21-2021, 10:14 AM
  2. Get row number of first empty cell in column and store that value in other cell
    By senk007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2020, 01:51 PM
  3. [SOLVED] Store a cell's value to a new row in a specific column every time that cell changes
    By esein in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-10-2018, 03:32 AM
  4. Replies: 1
    Last Post: 04-07-2014, 04:17 PM
  5. TO sum up a range in a column and store it in a cell in the next workbook
    By rshnkmr39 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-27-2013, 07:05 AM
  6. Replies: 5
    Last Post: 02-20-2013, 05:41 PM
  7. How to count all dates in column A using last friday and and next friday friday
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-16-2011, 04:33 PM

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