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

Do you have a range named APL in that worksheet?

If you do, maybe:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Me.Range("Ae1"), Unique:=True

End Sub

If you don't, then what's the range you want filtered?

and more important...

Dim APL as iRange
compiles for me, but I've never seen that type of variable. What is it?



MikeF wrote:
>
> 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

--

Dave Peterson

Dave,
Thanx for the reply.
Yes, the source range is named APL.
And iRange is a typo.

Tried this as below, get the same 1004 error msg as preivous when adding a
new number in the APL [source] range.


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim APL As Range

Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Me.Range("Ae1"), Unique:=True

End Sub



"Dave Peterson" wrote:

> Do you have a range named APL in that worksheet?
>
> If you do, maybe:
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
> CopyToRange:=Me.Range("Ae1"), Unique:=True
>
> End Sub
>
> If you don't, then what's the range you want filtered?
>
> and more important...
>
> Dim APL as iRange
> compiles for me, but I've never seen that type of variable. What is it?
>
>
>
> MikeF wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>

Dave,

Your solution works, I had to adjust another typo [agh!].
Thank you!!

PS -- Now if I could just get the output range to be a another sheet!!

"Dave Peterson" wrote:

> Do you have a range named APL in that worksheet?
>
> If you do, maybe:
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
> CopyToRange:=Me.Range("Ae1"), Unique:=True
>
> End Sub
>
> If you don't, then what's the range you want filtered?
>
> and more important...
>
> Dim APL as iRange
> compiles for me, but I've never seen that type of variable. What is it?
>
>
>
> MikeF wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>

What is the range that APL points to?

When I did my simple test, I selected A1:A10 and named it APL. And it worked
fine.

MikeF wrote:
>
> Dave,
> Thanx for the reply.
> Yes, the source range is named APL.
> And iRange is a typo.
>
> Tried this as below, get the same 1004 error msg as preivous when adding a
> new number in the APL [source] range.
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim APL As Range
>
> Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
> CopyToRange:=Me.Range("Ae1"), Unique:=True
>
> End Sub
>
> "Dave Peterson" wrote:
>
> > Do you have a range named APL in that worksheet?
> >
> > If you do, maybe:
> >
> > Option Explicit
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
> > CopyToRange:=Me.Range("Ae1"), Unique:=True
> >
> > End Sub
> >
> > If you don't, then what's the range you want filtered?
> >
> > and more important...
> >
> > Dim APL as iRange
> > compiles for me, but I've never seen that type of variable. What is it?
> >
> >
> >
> > MikeF wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Me.Parent.Worksheets("sheet2").Range("Ae1"), Unique:=True

End Sub

ps. You don't need the Dim statement.


MikeF wrote:
>
> Dave,
>
> Your solution works, I had to adjust another typo [agh!].
> Thank you!!
>
> PS -- Now if I could just get the output range to be a another sheet!!
>
> "Dave Peterson" wrote:
>
> > Do you have a range named APL in that worksheet?
> >
> > If you do, maybe:
> >
> > Option Explicit
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
> > CopyToRange:=Me.Range("Ae1"), Unique:=True
> >
> > End Sub
> >
> > If you don't, then what's the range you want filtered?
> >
> > and more important...
> >
> > Dim APL as iRange
> > compiles for me, but I've never seen that type of variable. What is it?
> >
> >
> >
> > MikeF wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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?

"MikeF" wrote:

> 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
>
>

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?
>
> "MikeF" wrote:
>
> > 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
> >
> >

--

Dave Peterson

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" wrote:

> 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?
> >
> > "MikeF" wrote:
> >
> > > 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
> > >
> > >
>
> --
>
> Dave Peterson
>

This seemed to work ok for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

.Sort.SortFields.Add _
Key:=.Range("Ae1"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

With .Sort
.SetRange .Parent.Range("Ae1")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Application.ScreenUpdating = True

End Sub

Don't you have a header in AE1 from the advanced filter? I changed that .header
parm to xlyes.


MikeF wrote:
>
> 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" wrote:
>
> > 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?
> > >
> > > "MikeF" wrote:
> > >
> > > > 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
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson