Filter problem

[bakerman]

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

 

[JamieDuncan]

What are your filter requirements?

 

[bakerman]

I want to fiter by a persons name. This is in colloum "F"

 

[JamieDuncan]

If you are only filtering by name, then what is the data validation drop down box for?

 

[bakerman]

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]

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]

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]

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]

Thanks, I managed to get it working. Watch out for the spelling mistake "modulle"