Help on Worksheet_change (I think!)

  • Thread starter Thread starter Colin Foster
  • Start date Start date
C

Colin Foster

Hi,

Using Excel 2007

I have a macro (Sub UpdatePrices() )with the following code...

Range("B199:b279").Select
Selection.Copy
Range("xfd199").End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("xfd199").End(xlToLeft).Select
If ActiveCell.Column < 31 Then Exit Sub
ActiveCell.Offset(0, -30).Select
Selection.Copy
Range("c126").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.ScreenUpdating = True

which I need to run everytime the value in cell A126 changes (it's based on
a live feed & updates every second)

I think what I need to do is incorporate Worksheet_Change into this somehow
using cell A126 as the key cell. However, elsewhere in teh worksheet this Sub
is used & when I tried to incorporate "my" macro into a new Worksheet_Change
Sub I received an error telling me that there was an ambiguous reference (or
words to that effect!)

Do I need to incorporate the bit about A126 into the first Worksheet_Change
& then tell it to run "my macro"? If so, suggestions welcome!

Cheers
Colin Foster
 
Your live feed may not activate the change event anyway. In some other cell,
say Z100, enter:

=A126

When A126 changes, Z100 will change also. Now we can use the Calculate
event macro to run your bit of code.
 
Hi Gary,
Thanks for this... so I can arrange the "Z100" bit (in fact A126 contains
=D2 which is where the "real" time is, so I'm actually there), but then how
do I build the Calculate Event Macro in to my existing code?

Thanks is advance

Regards
Colin
 
First make sure that Sub UpdatePrices() is in a standard module. Then insert
the following event macro in the worksheet code area:

Private Sub Worksheet_Calculate()
Call UpdatePrices
End Sub
 
Hi Gary,
Thanks for the swift reply.

"Worksheet code area" presumably the code bit attached to Sheet1 (where the
data is?
Also, don't I have to refer to the cell that I need to know has changed (A126?

Cheers
Colin
 
Back
Top