Can anyone point me in the right direction please?
Here is a modified Sub, with changes marked by "new".
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long 'new
If Target.Address = "$A$2" Then
Application.ScreenUpdating = False
With Sheets("sheet1")
.Range("A22").Copy
.Range("F2").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
iRow = Sheets("Sheet2").Range("A65536").End(xlUp).Row 'new
Sheets("Sheet2").Range("A" & iRow + 1).PasteSpecial
Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'new
Application.ScreenUpdating = False
Application.CutCopyMode = False 'new
End If
End Sub
By the way, I saw no need for the special Worksheet_Calculate()
Hth,
Merjet
|