This code must be placed in the “Sheet” and not in “Module1”. See pic.
Whenever something is added to “Sheet5” the macro will run automatically, coping the content from sheet 5 to sheet 6 cell B1.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws5 As Worksheet
Dim ws6 As Worksheet
Set ws5 = Worksheets(“Sheet5”)
Set ws6 = Worksheets(“Sheet6”)