Automated data transfer with date

[diku]

Hi
Very useful forum for user like me. I have problem like following:

I have some value in D12 cell which changes every hour due to some inputs.
I would like to record it automatically on another worksheet by everday 09:00 hrs.

So i need (D12) value in A1....Ax... and
on B1.... Bx reflects date for the same value.
each day it will change to next raw automatically and previous data will be intact.
Which helps me to prepare a chart from that data for further analysis.

Hope i explained it properly.

Regards.

 

[diku]

No feedback guys...
It seems either i have not posted my question properly or its too difficult?

Appreciate your feedback ASAP.

Hi
Very useful forum for user like me. I have problem like following:

I have some value in D12 cell which changes every hour due to some inputs.
I would like to record it automatically on another worksheet by everday 09:00 hrs.

So i need (D12) value in A1....Ax... and
on B1.... Bx reflects date for the same value.
each day it will change to next raw automatically and previous data will be intact.
Which helps me to prepare a chart from that data for further analysis.

Hope i explained it properly.

Regards.

 

[Saagar]

This can be done... but a clarification is needed. You mentioned the value changes every hour, but did you want to capture the value that is at 9:00am only? That is what I understand from your request - "I would like to record it automatically on another worksheet by everday 09:00 hrs. "

If you wanted all changes recorded through the day, do you want to capture the time in column B along with the date?

 

[diku]

This can be done... but a clarification is needed. You mentioned the value changes every hour, but did you want to capture the value that is at 9:00am only? That is what I understand from your request - "I would like to record it automatically on another worksheet by everday 09:00 hrs. "

If you wanted all changes recorded through the day, do you want to capture the time in column B along with the date?

Thanks for your responce. Actually i lost hope for that....
Yes you understood well. I need data to becaptured exactly at 09:00 hrs only and corresponding date shoould be recorded on column B

Thanks

If needed i will attach a file for reference.

Regards

 

[Saagar]

Test this on a copy of your workbook and let me know if it works the way you want.

Right click on the sheet where you want to capture D12 value and select "View Code". Paste the following code in the VBA window.

Make sure you insert a new spreadsheet in the workbook and leave it blank. Name the sheet "D12Values". You may use a different name but make sure you update the corresponding value in the code below.

The code will capture the value at 9:00am. For example, if D12 was "999" at 8:45am and "789" at 9:15am, it will save the value "999". The code will capture value, date and time in columns A,B and C respectively starting from Row2 in "D12Values" sheet. It also uses Cells F1:I2 in "D12Values" sheet to store current values.

Private Sub Worksheet_Change(ByVal Target As Range)

logtime = 32400     'seconds elapsed since midnight at 9:00am
logsheet = "D12Values" 'sheet name where data is archived
capturerow = 2

If Not Intersect(Target, Cells(12, "D")) Is Nothing Then
    Sheets(logsheet).Cells(capturerow, 6) = Cells(12, 4)
    Sheets(logsheet).Cells(capturerow, 7) = Date
    Sheets(logsheet).Cells(capturerow, 8) = Time()
    Sheets(logsheet).Cells(capturerow, 9) = Timer()
    
    currow = Sheets(logsheet).Range("B65536").End(xlUp).Row
    
    If Timer() > logtime And Not (Sheets(logsheet).Cells(currow, 2) = Date) Then
        Sheets(logsheet).Cells(currow + 1, 1) = Sheets(logsheet).Cells(capturerow, 6)
        Sheets(logsheet).Cells(currow + 1, 2) = Sheets(logsheet).Cells(capturerow, 7)
        Sheets(logsheet).Cells(currow + 1, 3) = Sheets(logsheet).Cells(capturerow, 8)
    End If
End If

End Sub

 

[diku]

Thanks Saagar

Let me test it for couple of days and i will give you feedback.

Heartly appreciate your effors.

Test this on a copy of your workbook and let me know if it works the way you want.

Right click on the sheet where you want to capture D12 value and select "View Code". Paste the following code in the VBA window.

Make sure you insert a new spreadsheet in the workbook and leave it blank. Name the sheet "D12Values". You may use a different name but make sure you update the corresponding value in the code below.

The code will capture the value at 9:00am. For example, if D12 was "999" at 8:45am and "789" at 9:15am, it will save the value "999". The code will capture value, date and time in columns A,B and C respectively starting from Row2 in "D12Values" sheet. It also uses Cells F1:I2 in "D12Values" sheet to store current values.

Private Sub Worksheet_Change(ByVal Target As Range)
 
logtime = 32400     'seconds elapsed since midnight at 9:00am
logsheet = "D12Values" 'sheet name where data is archived
capturerow = 2
 
If Not Intersect(Target, Cells(12, "D")) Is Nothing Then
    Sheets(logsheet).Cells(capturerow, 6) = Cells(12, 4)
    Sheets(logsheet).Cells(capturerow, 7) = Date
    Sheets(logsheet).Cells(capturerow, 8) = Time()
    Sheets(logsheet).Cells(capturerow, 9) = Timer()
 
    currow = Sheets(logsheet).Range("B65536").End(xlUp).Row
 
    If Timer() > logtime And Not (Sheets(logsheet).Cells(currow, 2) = Date) Then
        Sheets(logsheet).Cells(currow + 1, 1) = Sheets(logsheet).Cells(capturerow, 6)
        Sheets(logsheet).Cells(currow + 1, 2) = Sheets(logsheet).Cells(capturerow, 7)
        Sheets(logsheet).Cells(currow + 1, 3) = Sheets(logsheet).Cells(capturerow, 8)
    End If
End If
 
End Sub