Using Advanced Filter through Macro

[Guest]

I am a novoice in VBA, trying to run a macro using Advanced filter.

If someone can help me find the reason for error and means to overcoem it.

Dim wsData as Worksheet
Dim wsList As Worksheet
Set wsData = Worksheets("Data")
Set wsList = Worksheets("List")

If Target.Address = "$C$9" Then

wsData.Columns("A:Z").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=wsList.Range("J1:J2"), _
CopyToRange:=wsList.Range("A1"), Unique:=False
' ( system gives error on advanced filter condition)
End If
End Sub

thanks

 

[Mike Fogleman]

Because of a major shortcoming (in my opinion) of Advanced Filter, you
cannot copy/paste to another worksheet!! You will need to paste it out in
right field somewhere on the same sheet, then copy/paste that to the other
sheet.

Mike F

 

[Dave Peterson]

If you start the data|filter|advanced filter on the other worksheet, it should
work ok.

 

[Dave Peterson]

Your code worked ok for me.

(I dropped the target.address stuff and just ran it from a general module.)

If you did the same thing manually, do you get an error?

If yes, what's that error say?

 

[Debra Dalgleish]

What error do you get?
Is cell A1 on Lists empty, or does the heading match a heading in the
list on the Data sheet?