code to detect cell range change

i am trying to have a change in the range of a worksheet to activate a macro that copies, paste and sort the data from that range into another one. the copy/paste/sort macro portion works well but the code to detect the range change and call up the macro doesn't. I'm getting a "run-time 424 error". I ran this once and it worked but hasn't since. I have the following:

Please Login or Register  to view this content.

Here's what i've tried:

loaded ms DAO object library
used the "Private Sub Worksheet_Change()" event

Please note the macro to copy/paste/sort is in a module.

This is a sheet specific macro that runs whenever your worksheet calculates, normally whenever you make a change.

You should be use a selection_change event instead.

The macro will run another macro if you have selected the range O30 to O70.

Now is that what you wanted?

mehmetcik,

did changing the worksheet event work for you? i tried your suggestion and didn't have any luck.

olly,

i've tried the "worksheet_change" event before and again recently. i don't get an error msg but the code
doesn't run the macro. nothing happens. must be a glitch or i'm missing something

Rachee, as far as I know, changes made by calculation do not catched by Worksheet_Change event. And as far as Worksheet_Calculate event do not have Target, you can only choose to run your macro on EVERY recalculating, or, if you need to catch changes in determined range, I suppose that global public variable, which filled with values on Workbook_Open and updated after calculation, could help.

olly

this is just an example of what i need to happen