|re: The data is basic numbers that just happen to be related to those months. So system date may not be needed? I think?|
Regardless of whether the system date is used or something else is used, the code stills needs somthing to tell it where to place the next value entered into K10 and when to replace the January value with a new one. Think about it logically. Once A31 through A42 are filled with the first 12 pieces of data, how will the VBA code know that the next entry into K10 needs to go into A31? The code can't read the user's mind, so it has to be told in some way.
Since the entry of the data is not actually date related, we need a method other than the system date. For that we can use a counter. Let the counter start at 1 for the first entry, add 1 to it each time K10 is changed and reset it to 1 as soon as it reaches 13. The counter will continually count from 1 to 12, which we can use to determine which row to place the K10 data in.
Now, since VBA can not store any values once it is done running after each change to K10, we need to store the counter in a cell in the spreadsheet so that it is always available for the VBA code to read. In addition, if we add 30 to the counter, then we can use that number as the row number for the placement of the data. In other words, when the counter reads 1, 1 + 30 is 31, so the code will place the new data in A31. when the counter reads 12, 12 + 30 is 42, so the code will place the new data in A42. The next time K10 is changed, the counter will reach 13, be reset to 1, 1 + 30 is 31 and the data will be placed in A31, overwriting the current value in that cell.
The counter can be placed in any cell, it can be hidden, it can placed out of the normal viewing range, etc. For the purposes of this example, I put the counter in L10, so you can see it change whenever you enter a value in K10.
The only issue with this method is that if the counter is changed by the user it will screw up the VBA code since the counter is used to determine the next row number for the data placement. That problem can be solved by locking the cell with the counter, unlocking all other cells and then password protecting the worksheet if need be.
Try this code and let me know if it accomplishes your goal.
Private Sub Worksheet_Change(ByVal Target As Range)
'Make sure only the Target cell (K10) has changed
If Target.Address = "$K$10" And Target.Cells.Count = 1 Then
'Increment L10 counter, Reset it when it reaches 13
Cells(10, 12) = Cells(10, 12) + 1
If Cells(10, 12) = 13 Then Cells(10, 12) = 1
'Use L10 counter value to copy Target to correct row (31 To 42)
nxtRow = Cells(10, 12) + 30
Cells(nxtRow, 1) = Target
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.