PDA

View Full Version : [SOLVED] Advance Filter Copy to Range



brorick
07-23-2004, 08:47 AM
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
07-23-2004, 10:22 AM
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
07-23-2004, 10:37 AM
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
07-23-2004, 10:39 AM
Thank you Matt and Tommy. Both of your ideas worked like a charm. I am most greatful. Have a great day.

Zack Barresse
07-23-2004, 10:49 AM
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
07-23-2004, 11:07 AM
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