chrisjwhite90
New Member
- Joined
- Jan 17, 2013
- Messages
- 48
Hello,
I am just started learning VBA and needed some help on the creation of a macro to record a set of values and put in a time stamp as well.
The spreadsheet is set up with the second row (row 2) with a value in each cell going horizontally, from column to column. These cells are continuously updated with a link to provide a live number of a specific values, whose names are in row 1 above it.
I would like to record each of these values at the same time, every single 1 minute time interval, to keep a log of them. As the values change/update, I'd like all the values in the 2nd row (the ones that are continuously updating) to be copied and pasted to the row below, and in the first column (column A) have a time stamp of the exact time(every minute) that this value was recorded. At the next minute, I want this to happen again, but this time the value pasted below the previously recorded/pasted row (so lets say at 12:00:00 the values are pasted in row 3, and at 12:01:00 the values are copied and pasted into row 4), so that as time passes. a new row is formed with new snapshot values of the live cells in row 2. I want it to be able to run continuously (infinitely recording values downward) until I manually break the macro myself (I know this will be a huge amount of data, but I can manage that).
Also, how would I be able to adjust the spreadsheet macro in case I decide to add even more updating cells in row 2, thereby using more columns to record data. So for example, at first I'm keeping track of let's say 10 variables, range(B2:K2), but next week I want to keep track of range(B2:BB2). Is there a specific way I can update the macro in order to run it the same, but now also incorporate the new value recorded and time stamped, just like the others ( I realize that the time stamps for these "extra" products added in will not have the previously recorded data as the others would, and therefore would start way down the rows in sync with the others based on the time I start recording), and where specifically would I change this value to incorporate the new range? I want this all contained within the same sheet of the workbook, to automatically record even as I do other stuff in other workbooks/worksheets.
This being said, I started some code, so I don’t have to start from scratch. Please see the attached code (below) to give the best advice.
The function/macro "LastRow" was one I received from some help on another website, to add into mine: Last Used Column in Row
• ExcelKey.com But I don't know how to implement it accurately to get the results I want. Do I just add the function underneath, and which part of the code do I change to fit my needs? I would appreciate any step by step help on this.
I also received some other code to add a time stamp to each row, but I don't know how to factor it into the macro to make it work. Add The Date To Each Row Automatically As You Create Entries • ExcelKey.com I need help compiling this huge project to make it work efficiently.
I know how to make a button of this to initiate the macro, so that won’t be a problem.
I'm at a loss on how to do something this complex, and help or guidance would be appreciated. I hope I've been descriptive enough. Thanks!
CODE: (this is all I have so far)
Sub ValueStore()
Dim dTime As Date
Dim R As Long
R = LastRow("E") + 1
Cells(R, "B").Value = Range("B2").Value
Cells(R, "C").Value = Range("C2").Value
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub
I am just started learning VBA and needed some help on the creation of a macro to record a set of values and put in a time stamp as well.
The spreadsheet is set up with the second row (row 2) with a value in each cell going horizontally, from column to column. These cells are continuously updated with a link to provide a live number of a specific values, whose names are in row 1 above it.
I would like to record each of these values at the same time, every single 1 minute time interval, to keep a log of them. As the values change/update, I'd like all the values in the 2nd row (the ones that are continuously updating) to be copied and pasted to the row below, and in the first column (column A) have a time stamp of the exact time(every minute) that this value was recorded. At the next minute, I want this to happen again, but this time the value pasted below the previously recorded/pasted row (so lets say at 12:00:00 the values are pasted in row 3, and at 12:01:00 the values are copied and pasted into row 4), so that as time passes. a new row is formed with new snapshot values of the live cells in row 2. I want it to be able to run continuously (infinitely recording values downward) until I manually break the macro myself (I know this will be a huge amount of data, but I can manage that).
Also, how would I be able to adjust the spreadsheet macro in case I decide to add even more updating cells in row 2, thereby using more columns to record data. So for example, at first I'm keeping track of let's say 10 variables, range(B2:K2), but next week I want to keep track of range(B2:BB2). Is there a specific way I can update the macro in order to run it the same, but now also incorporate the new value recorded and time stamped, just like the others ( I realize that the time stamps for these "extra" products added in will not have the previously recorded data as the others would, and therefore would start way down the rows in sync with the others based on the time I start recording), and where specifically would I change this value to incorporate the new range? I want this all contained within the same sheet of the workbook, to automatically record even as I do other stuff in other workbooks/worksheets.
This being said, I started some code, so I don’t have to start from scratch. Please see the attached code (below) to give the best advice.
The function/macro "LastRow" was one I received from some help on another website, to add into mine: Last Used Column in Row
• ExcelKey.com But I don't know how to implement it accurately to get the results I want. Do I just add the function underneath, and which part of the code do I change to fit my needs? I would appreciate any step by step help on this.I also received some other code to add a time stamp to each row, but I don't know how to factor it into the macro to make it work. Add The Date To Each Row Automatically As You Create Entries • ExcelKey.com I need help compiling this huge project to make it work efficiently.
I know how to make a button of this to initiate the macro, so that won’t be a problem.
I'm at a loss on how to do something this complex, and help or guidance would be appreciated. I hope I've been descriptive enough. Thanks!
CODE: (this is all I have so far)
Sub ValueStore()
Dim dTime As Date
Dim R As Long
R = LastRow("E") + 1
Cells(R, "B").Value = Range("B2").Value
Cells(R, "C").Value = Range("C2").Value
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub
