Dave,
My last msg was incorrect.
First off, I'm back on the routine that outputs to the same page, just for
temporary ease of use.
It's not the cursor that moves, it's merely the screen that "moves" so the
output range can be viewed every time the worksheet routine fires. The cursor
actually stays where it was. I could live with that if the worksheet routine
fired only when I changed a cell in the APL range.
But have discovered the real problem is that the worksheet routine fires
every time ANY cell on the sheet is changed. The source sheet, where APL
resides, is also a data-entry sheet for hundreds of cells, so it's quite
disruptive every time ANYTHING changes.
I guess the question is --- is there any way to have the worksheet routine
fire ONLY when something in APL changes?
Or if not - can the worksheet routine fire "quietly"?
Also ---
Do you have any other worksheet events firing? No.
Did you add anything else to the worksheet_change event?
Yes, a routine that sorts the output in descending order.
'ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"Ae1")SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("Ae1")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Dave Peterson said:
I couldn't get the cursor to move to a different cell.
Do you have any other worksheet events firing?
Did you add anything else to the worksheet_change event?
MikeF wrote:
Dave,
Thank you, that's great.
If I may bother you for one more detail [today!] that just came up as a
result of this ...
Now the active cell moves to the output range, as opposed to staying within
the source range where I made the change, which is what would be ideal.
Is there any way to make the routine "run quiet", thereby not moving to the
output range/sheet?
:
Sort of a re-post:
Am looking to automatically extract unique values when anything in the
source list changes.
Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike
Private Sub Worksheet_Change(ByVal Target As Range)
Dim APL As IRange
Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True
End Sub