Macro/VBA for Advanced Filter

Hummel

New Member
Joined
Nov 7, 2004
Messages
13
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 - :)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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"
 
Upvote 0
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
ABCDEFGH
1DATABASE - Sample
2RefStartEndCYCYAmountRateCCYCCYAmount
3101-Jan-0829-Feb-08USD1,000,000.001.95GBP512,820.51
4201-Feb-0831-Mar-08EUR1,000,000.001.55GBP645,161.29
5301-Mar-0830-Apr-08SGD1,000,000.002.35GBP425,531.91
6401-Jan-0829-Feb-08USD1,000,000.001.95GBP512,820.51
7501-Feb-0831-Mar-08EUR1,000,000.001.55GBP645,161.29
8601-Mar-0830-Apr-08SGD1,000,000.002.35GBP425,531.91
9701-Jan-0829-Feb-08USD1,000,000.001.95GBP512,820.51
10801-Feb-0831-Mar-08EUR1,000,000.001.55GBP645,161.29
11901-Mar-0830-Apr-08SGD1,000,000.002.35GBP425,531.91
121001-Jan-0829-Feb-08USD1,000,000.001.95GBP512,820.51
131101-Feb-0831-Mar-08EUR1,000,000.001.55GBP645,161.29
141201-Mar-0830-Apr-08SGD1,000,000.002.35GBP425,531.91
151301-Jan-0829-Feb-08USD1,000,000.001.95GBP512,820.51
161401-Feb-0831-Mar-08EUR1,000,000.001.55GBP645,161.29
171501-Mar-0830-Apr-08SGD1,000,000.002.35GBP425,531.91
181601-Jan-0829-Feb-08USD1,000,000.001.95GBP512,820.51
191701-Feb-0831-Mar-08EUR1,000,000.001.55GBP645,161.29
DATAMML
 
Upvote 0
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
 
Upvote 0
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.

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top