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]

Yes i can be hidden..

Is it an absolute cell reference you want to track changes for?

I.E. Any time cell A1 cahnges you want to save a history of all values in a hidden sheet?

If so yes thats quite possible.

 

[nawaab007]

Yes i can be hidden..

Is it an absolute cell reference you want to track changes for?

I.E. Any time cell A1 cahnges you want to save a history of all values in a hidden sheet?

If so yes thats quite possible.

Yeah it is an absolute. Pls guide.

 

[Sthrncali]

Right Click on the Sheet Name of your data entry sheet (the one your users interact with) and paste this code in. There are comments advising the values you need to update for your worksheet.

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("A1:A1")) Is Nothing Then 'Change "A1:A1" to your cell range that you want to track changes for.
Exit Sub
End If
myStr = Target.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]

thanks for the message. i followed the exact steps you said. but the data is not copied in Sheet3. The code I pasted is below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myStr As String
Dim myDest As Worksheet
Set myDest = Sheet3
If Intersect(Target, Range("H33:H33")) Is Nothing Then 'Change "A1:A1" to your cell range that you want to track changes for.
Exit Sub
End If
myStr = Target.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]

Is there a mistake I am making?

 

[Sthrncali]

Sheet3 is referring to the Index Sheet3, not name "Sheet 3".... Are you sure there is not confusion on that part? I am not sure what your workbook looks like..

Maybe change that line to avoid any possible confusion?


Set myDest = Worksheets("YourSheetName")

 

[nawaab007]

No. The syntax is as you said. My sheet is attached. Could you pls guide.
http://www.4shared.com/document/9fo6mE9y/a_online.html

 

[Sthrncali]

It worked fine for me... Are you sure cell H33 is the one you want to track? It doesn't look like anything goes in cell H33 on your sheet "First"?

Other thing to check would be to verify your Macros are enabled that could also prevent it from working correctly...

 

[nawaab007]

Can you pls share the file you made at nawab35@gmail.com

Thanks