View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
merjet merjet is offline
external usenet poster
 
Posts: 812
Default 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