Can I copy values entered in a cell into another sheet?

[nawaab007]

Hi,

I have a sheet whereby values are entered in a cell in a sheet. I mean values in a cell are modified by the user. What I want is to store all the values that have been entered in that cell to be copied in another sheet so as to keep record of the historical values. Also could this sheet be made hidden?

Regards,
Nawaab007

 

[Sthrncali]

http://iput.it/d702f7

If changing Cell A1 doesnt update the list for you on Sheet 3, then your macros are disabled. Search Google for instructions on enabling macros in your version of Excel.

 

[nawaab007]

Oops I think I found the issue. The value is being changed by the formula. Would that be the show stopper?

 

[Sthrncali]

Yes... Formulas changing the cell value won't trigger than code. What is the formula/code that updates the cell value?

 

[nawaab007]

The formula in that cell is "=AVERAGE(C19:C24)".

 

[Sthrncali]

Are C19:C24 updated via User Input then??

If Intersect(Target, Range("C19:C34")) Is Nothing Then 
....
myStr = ActiveSheet.Range("H33").Value

 

[nawaab007]

Yes the range you mentioned is user updated. I did not follow the code snippet you sent. Pls guide.

 

[Sthrncali]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myStr As String
Dim myDest As Worksheet
Set myDest = Sheet3 'Change Sheet3 to your HIDDEN WORKSHEET.
If Intersect(Target, Range("C19:C24")) Is Nothing Then 'Change "A1:A1" to your cell range that you want to track changes for.
Exit Sub
End If
myStr = ActiveSheet.Range("H33").Value
myDest.Range("A10000").End(xlUp).Offset(1, 0).Value = myStr 'The code will start on cell A10000 and then move up to the last cell with an entry, and will add the new entry directly below. Change 10000 to a number that is greater than you would ever expect to track changes for. Also change A to the column you want to track changes in.

End Sub

 

[nawaab007]

I get the logic what you are trying to implement. But I cannot see a new entry whenever I change values in C19:C24. Could you pls again share the worksheet that incorporates change in value through a formula.

 

[Sthrncali]

Here:

http://iput.it/623083

Cell H33 was not correct. Your formula "=Average(C19:C24)" was located in cell E20.

So the correct code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myStr As String
Dim myDest As Worksheet
Set myDest = Worksheets("Second")
If Intersect(Target, Range("C19:C24")) Is Nothing Then 'Change "A1:A1" to your cell range that you want to track changes for.
Exit Sub
End If
myStr = ActiveSheet.Range("E20").Value
myDest.Range("A10000").End(xlUp).Offset(1, 0).Value = myStr 'The code will start on cell A10000 and then move up to the last cell with an entry, and will add the new entry directly below. Change 10000 to a number that is greater than you would ever expect to track changes for. Also change A to the column you want to track changes in.
End Sub

If you change a value in C19:C24, the list updates with the tracked changes on Sheet2. I left the history it generated when I plugged in different values to C19:C24 for you to see.

 

[nawaab007]

Phew!!!
Got it eventually. Thanks for your help.
You are awesome.