How to replicate the contents of a cell in one sheet to another sheet dynamically?

Your attempt was not far off.

I'll show you a way to get it working (as long as I understood your requirements) and after that I'll provide some explanation to the changes.

Try changing the code to this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address(False, False) = "M20" Then
        Exit Sub
    Else
        If Target.Value = "N" Then
            Sheets("Qu Data").Range("K8") = "N"
        ElseIf Target.Value = "Y" Then
            Sheets("Qu Data").Range("K8") = "Y"
        End If
    End If
End Sub

This will

• Check if the Target.Address is (well, is not) "M20"
• If it's not "M20" the sub is exited, this prevents the code from executing unnecessarily for example, when it assigns K8 with "Y" or "N".
• The rest is unchanged in logic, though I prefer to avoid references like [M20] and like to use `Range("M20") instead.

Documentation:

• Worksheet.Range Property
• Range.Address Property
• Not Operator

Why didn't your version work as intended?

A couple of key things:

Per the Range object (Excel) documentation:

The default member of Range forwards calls without parameters to the Value property ...

So that means

If Target.Range("M20") Then

is the same as

If Target.Range("M20").Value Then

I assume you intended for the code to be evaluating if the Target is Range("M20") and not the value of that range is True or False.

However, there is something else causing a deeper issue than that - This answer on my question about unexpected results using Target.Resize sheds some light on it.

If you were to create a new sheet and put Debug.Print Target.Range("M20").Address in the Worksheet_Change() event, you will see examples of what the above linked answer is talking about.

For example, If you change cell M20 you would see $Y$39 in the immediate window.

This is because the Target.Address is $M$20 and we are then referencing another range with $M$20 as the starting point - $Y$39 is 12 Columns right of M (M is 12 columns right of A) and 20 rows down (including row 20 - the same offset as row 20 from row 1).

So that would mean with your version of the code, If Target.Range("M20") Then is checking the value of Range("Y39"). Unless the Value of Y39 is True, your first If Then statement evaluates to False and thus nothing happens.

There are many ways you can construct your code to achieve what you want to do.

Here is another way to achieve your goal.

This solution;

• Allows handling of more than just Range M20 in the Worksheet_Change() event.
• Checks for any specified range addresses (per whatever you add in) and executes another Sub to handle whatever checks and actions are required.

Worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address(False, False)
    Case "M20"
        CheckCellM20 Target
    Case "C12"
        'Do some other routine if the change is in "C12"
    Case "A1"
        'Do some other routine if the change is in "A1"
    End Select
End Sub

Any Worksheet or standard module in the same project:

Public Sub CheckCellM20(ByVal TargetCell As Range)
    If TargetCell.Value = "N" Then
        Sheets("Qu Data").Range("K8") = "N"
    ElseIf TargetCell.Value = "Y" Then
        Sheets("Qu Data").Range("K8") = "Y"
    End If
End Sub

So the Worksheet_Change() code will now evaluate per the Select Case statement whether the Target.Address matches one of the case strings. If it does it will then execute the code on the next line, in the case of M20 it will execute the Sub CheckCellM20 and pass the Target range via the TargetCell attribute. This allows for the same code as you would write in the Worksheet_Change() sub but to handle it elsewhere.

This method becomes useful when you have several cell changes you want to handle as it prevents excess clutter in the Worksheet_Change() event code and makes it much clearer what you are intending to handle and how (this is probably something you will thank yourself for in a year or two!).

To address your comment:

Is there a way I could tweak it to extend to cells G19:G56 in Dashboard sheet so that each Y or N there corresponds to the respective cell in range K19:K56 in the Qu Data sheet.

I have written the following for the Worksheet_Change() event which does the following;

• Declares two constants, the range address you want to check for changes in, and the worksheet name the values will correspond to.
• Loops through the RangeToCheck checking if each Cell address matches the Target address for the changed worksheet cell that triggered the event.
• Once matched it will update the corresponding row in column K on the TargetWorksheet (by using the Range.Offset property).

Please Note:

• This will only handle a single cell change at a time.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const RangeToCheckAddress As String = "G19:G59"
    Const TargetWorksheetName As String = "Qu Data"
    Dim RangeToCheck As Range: Set RangeToCheck = Me.Range(RangeToCheckAddress)
    Dim CellToCheck As Range
    
    For Each CellToCheck In RangeToCheck
        If Target.Address = CellToCheck.Address Then
            If Target.Value = "Y" Or Target.Value = "N" Then
                Sheets(TargetWorksheetName).Range(CellToCheck.Address).Offset(0, 4).Value = Target.Value
                Exit Sub
            End If
        End If
    Next CellToCheck
End Sub

You could integrate this with the M20 check like so:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const RangeToCheckAddress As String = "G19:G59"
    Const TargetWorksheetName As String = "Qu Data"
    Dim RangeToCheck As Range: Set RangeToCheck = Me.Range(RangeToCheckAddress)
    Dim CellToCheck As Range

    Select Case Target.Address(False, False)
    Case "M20"
        CheckCellM20 Target
    Case Else
        For Each CellToCheck In RangeToCheck
            If Target.Address = CellToCheck.Address Then
                If Target.Value = "Y" Or Target.Value = "N" Then
                    Sheets(TargetWorksheetName).Range(CellToCheck.Address).Offset(0, 4).Value = Target.Value
                    Exit Sub
                End If
            End If
        Next CellToCheck
    End Select
End Sub

Public Sub CheckCellM20(ByVal TargetCell As Range)
    If TargetCell.Value = "N" Then
        Sheets("Qu Data").Range("K8") = "N"
    ElseIf TargetCell.Value = "Y" Then
        Sheets("Qu Data").Range("K8") = "Y"
    End If
End Sub