Capture value from a dynamic cell at a specific Hour and populate a column

**EEEHHH** · 02-12-2019, 02:26 PM

Hi,

Cell A1 values changes dynamically throughout the day till say 1550 hours.

I am looking to capture value from a dynamic cell (A1) at a specific hour say at 1600 hours.

The captured value will be saved in a column (B) everyday with date (column A) starting from say Row10.

Not able to attach a sample sheet as the attachment window is not working. A picture is attached instead.

Thanks,

Sample 1.jpg

**Bernie Deitrick** · 02-12-2019, 04:29 PM

The attach file button doesn't work - click "Go Advanced" to the lower right of the edit window, then use the "Manage attachments" option.

Save your file as a macro-enabled .xlsm and change "SheetName" in two places in the code to the actual sheet name.

With this in a regular code module:

Please Login or Register  to view this content.

And this in the codemodule of the ThisWorkbook object:

Please Login or Register  to view this content.

**EEEHHH** · 02-13-2019, 03:41 AM

Bernie Deitrick,

Thanks.

Attached is the Test.xlsm file with the codes.

It is not working.

I have changed the time to 1200 hours.

**Bernie Deitrick** · 02-13-2019, 12:46 PM

You put the event code into a standard codemodule rather than the Thisworkbook object's codemodule: I have attached a working version with one change that I made to make testing easier: instead of 1200/2400 I use TimeSerial(12,0,0) (if you wanted to run at 11:47 AM, you would not use 1147/2400: TimeSerial(11,47,0) will allow you to choose that as the exact time.

**EEEHHH** · 02-18-2019, 07:36 AM

hi,

It is working. Thanks for the effort and time.

Issues I noticed:
Right now it saves the required data when we close and reopen the file. It does not do it at the designated time when the workbook is open.
Also reopening the workbook multiple times on same date it shows multiple entries of the same date.

Though my basic requirement has been met it would be nice if above issues are addressed.

Thanks again,

**Bernie Deitrick** · 02-18-2019, 11:58 AM

Change RunMe to this to address the multiple saves:

Please Login or Register  to view this content.

The timing / schedule works for me: are you opening the workbook once in the morning and closing it after the time that you want to store the data?

**EEEHHH** · 02-19-2019, 06:21 AM

This solves multiple saves.

w.r.t. timing I will use it for a while and revert with observation.

Thanks,

Capture value from a dynamic cell at a specific Hour and populate a column

LinkBack

Thread Tools

Rate This Thread

Display

Capture value from a dynamic cell at a specific Hour and populate a column

Re: Capture value from a dynamic cell at a specific Hour and populate a column

Re: Capture value from a dynamic cell at a specific Hour and populate a column

Re: Capture value from a dynamic cell at a specific Hour and populate a column

Re: Capture value from a dynamic cell at a specific Hour and populate a column

Re: Capture value from a dynamic cell at a specific Hour and populate a column

Re: Capture value from a dynamic cell at a specific Hour and populate a column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to capture a max value in a dynamic cell

[SOLVED] Populate a specific cell with column heading name.

Command But to populate the next empty cell in a specific column with contents of Listbox

From the current cell, search up in the same column to specific cell then capture row #

how to capture data from dynamic cell on a condition

Capture values over 0 and populate results in another column

capture unique values from 1 column and populate the result into another column

Bookmarks

Bookmarks

Posting Permissions