Filter problem

bakerman

Board Regular
Joined
Sep 9, 2005
Messages
188
I have a worksheet with 25 colloums of data and an ever growing number of rows. What I want to do is, create a drop down list using data validation. The value in the drop down box would set an auto filter, I then want to copy the data brought back and copy it in to a new work sheet :unsure:
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
If you are only filtering by name, then what is the data validation drop down box for?
 

bakerman

Board Regular
Joined
Sep 9, 2005
Messages
188

ADVERTISEMENT

I want the end user to select a name from the drop down box, this will then populate a report on a seperate work sheet. What I think I need is some way of setting the auto filter criteria to the value in the drop down box
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
Just a few more things,

1. Where is the drop down box? (sheetname,range)
2. Will there be duplicate names in column F?
3. Is the filtered data goin to the same sheet each time, clearing before entering more data so that other sheets can take data from it?
 

bakerman

Board Regular
Joined
Sep 9, 2005
Messages
188

ADVERTISEMENT

The drop down bow is in a sheet named "View Report" Cell F6
There will be no duplicate names only seperate instances of the same name
The filter is going in to the same sheet each time
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
Ok this code does what i think you need it to.

You will have to copy the ENTIRE F column from what i have dubbed the 'input' sheet (where your original data is stored),
into the 'view report' sheet and link the cells, then it will update as
automatically with 'input' sheet.

Does not matter what column you put them and you can hide it by formatting it white.
For this example I will assume you will copy to column BB,

Validate Data for Cell F6 on the view data sheet as =BB2:BB65536
Starting at BB2 will not add headers to the selection in your drop down box.

Running this code will then create a sheet called 'selected data' which will
have the information you require


<font face=Courier New><SPAN style="color:#007F00">'Put this code into a module</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> Extract_Data()
    <SPAN style="color:#00007F">Dim</SPAN> ShTarget <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> FilterCriteria
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ShTarget = Worksheets("Selected Data")
    <SPAN style="color:#00007F">If</SPAN> Err = 0 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">With</SPAN> ShTarget
            .Cells.ClearContents
<SPAN style="color:#007F00">'            .Visible = False</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        Err.Clear
        <SPAN style="color:#00007F">Set</SPAN> ShTarget = Worksheets.Add
        <SPAN style="color:#00007F">With</SPAN> ShTarget
            .Name = "Selected Data"
<SPAN style="color:#007F00">'            .Visible = False</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
    <SPAN style="color:#00007F">Set</SPAN> Sh = Worksheets("Input")
    <SPAN style="color:#00007F">Set</SPAN> rng = Sh.Range("A1:Y65536")
    FilterCriteria = Worksheets("View Report").Range("F6")
    rng.AutoFilter
    rng.AutoFilter field:=1, Criteria1:=FilterCriteria
    rng.SpecialCells(xlCellTypeVisible).Copy ShTarget.Range("A1")
    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
    rng.AutoFilter
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Now if you want to run the macro automatically when the selection changes put this code into the 'view report' code


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("f6")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Or</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN>
Module1.Extract_Data
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Now whenever you change the selection in the data validation cell, it will

update the 'selected data' sheet.

Hope this is what you were after.
 

bakerman

Board Regular
Joined
Sep 9, 2005
Messages
188
Thanks, I managed to get it working. Watch out for the spelling mistake "modulle"
 

Forum statistics

Threads
1,137,298
Messages
5,680,689
Members
419,927
Latest member
Axtros

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
Top