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

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
"Prashant Garg" wrote in message
...

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

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

Mike Fogleman wrote:

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
"Prashant Garg" wrote in message
...

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

--

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?

Prashant Garg wrote:

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

--

Dave Peterson

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?

Prashant Garg wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html