Can anyone point me in the right direction please?

[jonathan]

my code writes a changing calculation value to another part of my
spreadsheet.
I am trying to amend my code to paste to another worksheet in the
workbook and also write to a different line each time the value changes


can anyone point me in the right direction

current code below



Private Sub Worksheet_Calculate()
Worksheet_Change Range("$A$2")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
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
Application.ScreenUpdating = False

End If

End Sub

thanks Jonathan

 

[merjet]

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

 

[jonathan]

Hi Merjet

Thanks i really appreciate your help

I understood that because the cell that changes is as result of a
formula and a dde link, worksheet_change won't recognise the
information and i have to use worksheet calculate. Can you clarify
this for me

regards


Jonathan

 

[merjet]

You're right. Your special Worksheet_Calculate is needed if A2 has a
formula and its value changes as a result of another cell changing.
Worksheet_Change fires only
when its Target is changed manually, and earlier I assumed changing A2
manually.

Hth,
Merjet

 

[jonathan]

Merjet
Thanks for your assistance its works great!!

One very happy person!!

Jonathan