Advance Filter Copy to Range

[brorick]

Hello. I am using the following code and it works the first time. The problem is, after it has copied the data to Sheet1, and I run the macro again with a different filter variable it won't repalce the existing data on Sheet1. If I delete the information on Sheet1 and then run the macro, everything is fine. I am puzzled. Any help is appreciated. :rolleyes:



Sheets("Database").Select
Range("data").Select
Range("A1:F14").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"K1:K2"), CopyToRange:=Sheets("Sheet1").Range("a1"), Unique:=False

[mvidas]

Hi Brorick,
You could always put a line like


Sheets("Sheet1").Cells.Delete

Above your AdvancedFilter line. Would clear the sheet beforehand
Matt

[tommy bak]

hi Brorick.
It looks/works fine to me.
I would omit the two first lines though and give the criteria-area a name too. (e.g. Crit1)



Range("Data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Crit1"), _
CopyToRange:=Sheets("Sheet1").Range("a1"), Unique:=False


br
Tommy Bak

[brorick]

Thank you Matt and Tommy. Both of your ideas worked like a charm. I am most greatful. Have a great day.

[Zack Barresse]

I would omit the two first lines though and give the criteria-area a name too. (e.g. Crit1)


Hi Tommy,

As I'm not too 'up to snuff' on my Filter code, I was wondering if you could explain this a little more. What are the advantages to that? Also, what requirements go along with naming the criteria? Additional steps?

[tommy bak]

Hi Firefytr

The advantage here is that you can omit the 2 selects (Selects are slowing down the code and can course c?nfusion.)

What I would normally do, is to give the criteria-area (and the database) dynamic names, so that the criteria area automatic expands/contracts, when I input more criteries.

I will gladly post an example file if you're interested. ( just have to translate it first unless you can read Danish) :-)

br
Tommy Bak