Recording an entire row of cells (with references to other cells) automatically when I change the referenced cells

Question

I have formulas that I need to change that have outputs and solutions. I have created an entire row A46:CV42 that shows every effected Value (formulas solution) in each cell. What I need is to record every single change to that entire row automatically every time any cell value is changed. Be it on the same sheet below it in recurring rows or on another sheet.

I have tried some things I have found but they all deal with individual cells being changed or when you manually change data.

Example of what I've tried. I am a novice in this area.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then
        a = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
        Sheets("Sheet2").Range("A" & a).Value = Sheets("Sheet1").Range("A").Value
    End If
End Sub

Answer 1

I'm not quite sure if you want row 46 or row 42. Please see below an approach, and comments in the code for further details:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wb As Workbook: Set wb = ThisWorkbook

    Dim wsWatch As Worksheet: Set wsWatch = wb.Sheets("Sheet1") 'declare and set the sheet (change the name or use Target.Worksheet instead as needed)
    Dim rngWatch As Range: Set rngWatch = wsWatch.Range("A46:CV46") 'declare and set the range to watch over
    Dim arrWatch As Variant: arrWatch = rngWatch 'allocate the range to an array

    Dim wsHistory As Worksheet: Set wsHistory = wb.Sheets("Sheet2") 'declare and set the sheet
    With wsHistory
        Dim lRow As Long: lRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'get the last row in the history
        Dim rngHistory As Range: Set rngHistory = .Range(.Cells(lRow, 1), .Cells(lRow, 100)) 'declare and set the range of the last populated row (last historic change)
    End With
    Dim arrHistory As Variant: arrHistory = rngHistory 'allocate the range to an array

    Dim C As Long

    'Only one row in the arrays, let's loop over the columns
    For C = LBound(arrWatch, 2) To UBound(arrWatch, 2) 'for each column in the ranges
        If arrWatch(1, C) <> arrHistory(1, C) Then 'if there is a mismatch
            rngHistory.Offset(1) = rngWatch.Value 'allocate the values in the next free row
            Exit For 'exit here if mismatch found
        End If
    Next C
End Sub

This might not be the ideal solution, but i'm looking forward to see other solutions... meanwhile, I think it does what you've asked.