Macro/VBA for Advanced Filter

[Hummel]

I need to solve the following either using a Macro or VBA using Autofilter.

Criteria
1. data is held in a Datasheet
2. Need to extract Data given a specific criteria e.g. say By a certain date
3. Copy all data that matches the criteria to a new workbook and Worksheet
4. The criteria is specified in the "new Workbook/Worksheet"

Note: In principal this will work with "Advanced Autofilter"

Solution Required.
1. That any user can change the criteria in the new Workbook" without going manually through the process of applying "Advanced Autofilter".

Please can anyone help
Please note I am not a VBA Guru, - so please be gentle -

 

[Hummel]

UPDATE TO ABOVE QUESTION.

What macro or VBA code could apply for the Advance Filter to "Dynamically Update" if the Criteria is changed. To reduce going through the manual process applying the "Advance Filter"

 

[Hummel]

UPDATE TO ABOVE QUESTION.

What macro or VBA code could apply for the Advance Filter to "Dynamically Update" if the Criteria is changed. To reduce going through the manual process applying the "Advance Filter"

Advance Filter and VBA2.xls
	A	B	C	D	E	F	G	H
1	DATABASE - Sample
2	Ref	Start	End	CY	CYAmount	Rate	CCY	CCYAmount
3	1	01-Jan-08	29-Feb-08	USD	1,000,000.00	1.95	GBP	512,820.51
4	2	01-Feb-08	31-Mar-08	EUR	1,000,000.00	1.55	GBP	645,161.29
5	3	01-Mar-08	30-Apr-08	SGD	1,000,000.00	2.35	GBP	425,531.91
6	4	01-Jan-08	29-Feb-08	USD	1,000,000.00	1.95	GBP	512,820.51
7	5	01-Feb-08	31-Mar-08	EUR	1,000,000.00	1.55	GBP	645,161.29
8	6	01-Mar-08	30-Apr-08	SGD	1,000,000.00	2.35	GBP	425,531.91
9	7	01-Jan-08	29-Feb-08	USD	1,000,000.00	1.95	GBP	512,820.51
10	8	01-Feb-08	31-Mar-08	EUR	1,000,000.00	1.55	GBP	645,161.29
11	9	01-Mar-08	30-Apr-08	SGD	1,000,000.00	2.35	GBP	425,531.91
12	10	01-Jan-08	29-Feb-08	USD	1,000,000.00	1.95	GBP	512,820.51
13	11	01-Feb-08	31-Mar-08	EUR	1,000,000.00	1.55	GBP	645,161.29
14	12	01-Mar-08	30-Apr-08	SGD	1,000,000.00	2.35	GBP	425,531.91
15	13	01-Jan-08	29-Feb-08	USD	1,000,000.00	1.95	GBP	512,820.51
16	14	01-Feb-08	31-Mar-08	EUR	1,000,000.00	1.55	GBP	645,161.29
17	15	01-Mar-08	30-Apr-08	SGD	1,000,000.00	2.35	GBP	425,531.91
18	16	01-Jan-08	29-Feb-08	USD	1,000,000.00	1.95	GBP	512,820.51
19	17	01-Feb-08	31-Mar-08	EUR	1,000,000.00	1.55	GBP	645,161.29
DATAMML

 

[channel7]

Have you solved your "Dynamically Update" problem yet?

 

[Hummel]

No I have not solved it yet

 

[Hummel]

Really what I am looking for is a dynamic Advanced Filter code.
when he user changes the criteria in the "criteria range" that the Advanced Filter is then automatically engaged, giving the new results in the result area, preferably without having to open the spreadsheet which holds the database. Hope this makes sense

 

[channel7]

Here is code I use to set the Autofilter in another worksheet based upon the cell value in a different workbook.

Column A in Book2 will Autofilter based upon the cell value of B1 in Book1.

Windows("Book2.xls").Activate
    Range("A:A").Select
    Selection.Autofilter Field:=1, _
Criteria1:=Workbooks("Book1.xls").Worksheets("Sheet1").Range("B1").Value
    Range("A1").Select

Is this similar to what you are needing? I am learning VBA myself and your post sounded familiar to something I was looking for earlier.

If this is not what you are needing maybe I have something else that will help you.