Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anyone point me in the right direction please?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anyone point me in the right direction please?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anyone point me in the right direction please?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anyone point me in the right direction please?
Merjet
Thanks for your assistance its works great!! One very happy person!! Jonathan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data point on line is not over the point/tick in X axis... | Charts and Charting in Excel | |||
Direction | Excel Discussion (Misc queries) | |||
I need more general XY point to point plotting than XY scatter in | Charts and Charting in Excel | |||
I need direction | Excel Discussion (Misc queries) | |||
tab direction | Excel Programming |