Copying a Changing Cell Content in One Sheet into a Column in a Different Sheet

[ainon123]

I have an Update Sheet where every week i update the content or value in a particular cell. I need the content to be then copied into a column in a History Sheet. That is, each row in the column in the History Sheet will display a week's updated value. So if i update the cell value 100 times in the Update sheet, i will have 100 rows of values automatically generated in the column in the History Sheet without me even needing to open the History Sheet

Please help.

 

[L. Howard]

Try this in the Update Sheet module.

The particular cell in this code is M5.
The column on History Sheet is column A.

Howard

Option Explicit

Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$M$5")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Target.Copy Sheets("History Sheet ").Range("A" & Rows.Count).End(xlUp)(2)
End Sub

 

[ainon123]

Try this in the Update Sheet module.

The particular cell in this code is M5.
The column on History Sheet is column A.

Howard

Option Explicit

Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$M$5")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Target.Copy Sheets("History Sheet ").Range("A" & Rows.Count).End(xlUp)(2)
End Sub

Thank you so much. It worked beautifully. However, if I want to start the column from row A8 for example, how do I achieve that?

 

[L. Howard]

Try this.

Howard

Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$M$5")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

If Sheets("History Sheet").Range("A8") = "" Then

   Target.Copy Sheets("History Sheet").Range("A8")
  
 Else

  Target.Copy Sheets("History Sheet").Range("A" & Rows.Count).End(xlUp)(2)

End If

End Sub

 

[ainon123]

Try this.

Howard

Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$M$5")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

If Sheets("History Sheet").Range("A8") = "" Then

   Target.Copy Sheets("History Sheet").Range("A8")
  
 Else

  Target.Copy Sheets("History Sheet").Range("A" & Rows.Count).End(xlUp)(2)

End If

End Sub

Thank you for your help once again, but only the first reading appears in Row A8. Subsequent rows do not appear even when the value in Cell M5 is changed. Also, if I want to copy another cell values (say N5) in the Update Sheet into another column in the History Sheet (say starting from Row B8), can I just copy the same code as another Sub?

 

[L. Howard]

The code works this way on my sheet.

Enter a value in M5 hit enter.
If cell A8 on History Sheet is empty, then that value goes in A8.
Enter another value in M5 and hit enter, that value goes the cell A9.

Repeat and cells A10 then A11, A12 etc. each get the next value in turn from cell M5.

Is that what you are doing?

You cannot copy Sub Worksheet_Change(ByVal Target As Range) for cell N5. You can only have one Worksheet_Change code in the sheet module.

We would have to modify the code within the sub to do the N5 caper.

Try this but delete the other code first.

Howard

Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$M$5,$N$5")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

If Target.Address = "$M$5" Then

  If Sheets("History Sheet").Range("A8") = "" Then

     Target.Copy Sheets("History Sheet").Range("A8")

   Else

    Target.Copy Sheets("History Sheet").Range("A" & Rows.Count).End(xlUp)(2)

  End If
  
 ElseIf Target.Address = "$N$5" Then

  If Sheets("History Sheet").Range("B8") = "" Then

     Target.Copy Sheets("History Sheet").Range("B8")

   Else

    Target.Copy Sheets("History Sheet").Range("B" & Rows.Count).End(xlUp)(2)

  End If
  
End If
End Sub

 

[ainon123]

The code works this way on my sheet.

Enter a value in M5 hit enter.
If cell A8 on History Sheet is empty, then that value goes in A8.
Enter another value in M5 and hit enter, that value goes the cell A9.

Repeat and cells A10 then A11, A12 etc. each get the next value in turn from cell M5.

Is that what you are doing?

You cannot copy Sub Worksheet_Change(ByVal Target As Range) for cell N5. You can only have one Worksheet_Change code in the sheet module.

We would have to modify the code within the sub to do the N5 caper.

Try this but delete the other code first.

Howard

Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$M$5,$N$5")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

If Target.Address = "$M$5" Then

  If Sheets("History Sheet").Range("A8") = "" Then

     Target.Copy Sheets("History Sheet").Range("A8")

   Else

    Target.Copy Sheets("History Sheet").Range("A" & Rows.Count).End(xlUp)(2)

  End If
  
 ElseIf Target.Address = "$N$5" Then

  If Sheets("History Sheet").Range("B8") = "" Then

     Target.Copy Sheets("History Sheet").Range("B8")

   Else

    Target.Copy Sheets("History Sheet").Range("B" & Rows.Count).End(xlUp)(2)

  End If
  
End If
End Sub

Thanks for your kind help. Actually, I decided to copy and paste the code you gave for the single cell to avoid mistyping errors and it worked very well. However, when I copied and pasted the code for the 2 cells example, the code did not work and nothing appeared in the History Sheet. I tried several times (copying and pasting) and each time, there were still no readings.

 

[L. Howard]

Check the name on the sheet tab named History Sheet. You may have a following space after the name on the sheet tab.

Howard

 

[L. Howard]

A two sheet demo workbook.

https://www.dropbox.com/s/0fb2m5i6r5c2xp9/Book1 M5 N5.xlsm?dl=0

Howard

 

[ainon123]

Check the name on the sheet tab named History Sheet. You may have a following space after the name on the sheet tab.

Howard

Thanks for your reply. I repeated several times. When I copied and pasted the one cell code, everything worked fine. However, when I changed (copied and pasted) the code to that given by you for the 2-cells example, nothing appeared in the same History Sheet.