Excel copy certain cell value to another cell/worksheet if cell value updated/changed-CodePudding

I try to create a project management database that I updated on daily basis.

What I have done and worked on so far right now is if the value in Sheet1 cell A1 is updated, the previous value in cell A1 will be copied to Sheet2 C1. And if I update again the value in Sheet1 cell A1, it will be copied again to Sheet2 C2, below the previous copy, and so on.

I want to apply the same code for the next cell, for example if Sheet1 A2 value is updated, it will be copied to Sheet2 D1, and will be copied down below previous copy (Sheet2 D2), if Sheet1 A2 value changed again.

So my question is, is there any way to simplify the code for input cell A1,A2,A3..and output cell C1,D1,E1.. because I know copy-paste and change target and destination address wouldn't work. Below is my code that can only work for Sheet1 target cell A1. I am really new in VBA so any response would be really great.

Private Sub worksheet_change(ByVal target As Range)
    If target.Address = Range("A1").Address Then
        Dim intLastRow As Long
        intLastRow = Sheet2.Cells(Sheet1.Rows.Count, "C").End(xlUp).Row
     
        Sheet2.Cells(intLastRow   1, "C") = target.Value
    End If
End Sub

CodePudding user response：

Test if the change happened in Row 1, then get the column and use that instead of the hardcoded "C".


Private Sub worksheet_change(ByVal target As Range)
    If target.Row = 1 Then
        Dim col As Long
        Dim intlastrow As Long
        
        col = target.Column   2 'Column to modify
        intlastrow = Sheet2.Cells(Rows.Count, col).End(xlUp).Row
        If intlastrow = 1 Then 'Populate Row 1 or increment
            If Sheet2.Cells(intlastrow, col).Value <> "" Then
                intlastrow = intlastrow   1
            End If
        else
            intlastrow = intlastrow   1
        End If
        Sheet2.Cells(intlastrow, col) = target.Value
    End If
End Sub