Hi,

I've got a spreadsheet that I want to log values from another worksheet when the value in Cell B4 changes. The trouble is that as it only references the cell ie.

 =Sheet1!B4
the macro to copy the text doesn't work.

The code I currently have is listed below


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Target = Cells(2, 4) Then
    Call CopyAndPaste
  Else
    Exit Sub
  End If

End Sub

Sub CopyAndPaste()

        Dim RowCount As Long
        
        Range("A5:P5").Copy
        
        RowCount = Application.WorksheetFunction.CountA(Range(Cells(1, "A"), Cells(Rows.Count, "A")))
        Range("A1").Offset(RowCount, 0).PasteSpecial (xlPasteValues)

End Sub
Any help on this would be much appreciated.

Many thanks,
twills