Auto filter a table based on the value of a cell in a different sheet

[kryptonian]

In Sheet1, I type in the following info: cell A1=EmployeeNumber, A2=Date, A3=Reason, A4=Tag. The info typed here are then saved to a database in Sheet2, i.e., column A=EmployeeNumber, column B=Date, column C=Reason, column D=Tag; this is done via macro when a SAVE button is clicked.

What I would like to happen is to filter the data in Sheet2 automatically depending on the data that I typed in A1, i.e., if I typed in 123456 in A1, I would like the table in Sheet2 to be filtered and only show all data for 123456.

Is this possible? Thanks in advance!

 

[Anthony47]

Try this...
-Right-click the tab with the name of sheet2 (ie "on Sheet2")
-the vba editor will open
-copy and past the following code in the blank frame at the right:

Private Sub Worksheet_Activate()
On Error Resume Next
    Selection.AutoFilter Field:=1, Criteria1:=Sheets("Sheet1").Range("A1").Value
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
    ActiveSheet.ShowAllData
End Sub

Return to excel and try.

Bye.

 

[kryptonian]

That was a quick one Anthony47! Exactly what I needed! Just a little more tweaking to make my file work the way I want it to!

 

[thestickhughes]

Hi, I know this is an old thread but it is so close to what i need. unfortunately my skill is very limited with the vba editor. The formula listed about seams to be almost exactly what i need but no matter what i try or how much i search google i cant get it to work.

I have a ever growing table where we log mileage from personal vehicles. I have a cell(B6) where one can enter a name and it will sum all the important data for that person. What i cant get to work to have that cell automaticly autofilter the table to only show the rows with that name in it.

My table headers are E10 thru P10, Date, Name, Location, Job, Description, Trips, Miles, Total Miles, Paid.
i want to autofilter the data to only show rows for the name entered in cell B-6
The table is called "Mileage" the sheet is "Sheet1"

sorry if that is not enough info to provide any advice back. i tried to attach a copy of the spreadsheet but was unable to

Tyler

 

[Anthony47]

If the solution proposed to kryptonian does not work for you it means that your question is different; and indeed it needs a different approach.
Rightclick on the tab with the name of the sheet with the table, select Display code
-the vba editor will open
-copy and past the following code in the blank frame at the right:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("E6")) Is Nothing Then
    Range(Range("F10"), Range("F10").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("E6").Value & "*"
End If
End Sub

When you modify cell E6 then the filter will be applied to the column starting in F10 and will select only the names that "begin" with the string in E6; if E6 is empty all the names will be shown.

Bye

 

[thestickhughes]

thank you Anthony47. that is exactly what i wanted. i spent close to 2 whole days reading forums and copying code but finally gave up and made macros to do what i needed. this works so much better. thank you.
If i decide to add another cell for more filtering i understand that i would need something like

Criteria2:=Range("Z3").Value & "*"

for a second criteria, but what do i put to have a second column to filter?

 

[Anthony47]

You can only filter adjacent columns;so in case you need to filter for example columns F and H you could use

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("E6")) Is Nothing Or _
   Not Application.Intersect(Target, Range("Z3")) Is Nothing Then
    Range(Range("F10"), Range("H10").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("E6").Value & "*"
    Range(Range("F10"), Range("H10").End(xlDown)).AutoFilter Field:=3, Criteria1:=Range("Z3").Value & "*"
End If
End Sub

First you have to catch change in either E6 or Z3 (the filtering values), than you will apply two filters as shown above.

Bye

 

[thestickhughes]

thanks again Anthony47. that works great

 

[R3Z]

Hi Antony47, I know this is an old post but this code really helped me at what I need to do.
But when I try to add a new row on the table, when I search the new data I don't get the result.
I think it have something to do with the cell format because I get the error "the number in this cell is formatted as text or preceded by an apostrophe" and when I search the data from that cells i get the result, when I add more rows in the table the new rows doesn't give me that error an i don't get the result when I insert the Value on the B2 cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("C2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("D2")) Is Nothing Then
    Range(Range("B5"), Range("D5").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("B2").Value & "*"
    Range(Range("B5"), Range("D5").End(xlDown)).AutoFilter Field:=2, Criteria1:=Range("C2").Value & "*"
    Range(Range("B5"), Range("D5").End(xlDown)).AutoFilter Field:=3, Criteria1:=Range("D2").Value & "*"
End If
End Sub

 

[Anthony47]

The value you type in B2-C2-D2 need to be of the same type of the data to be filtered; if they are Numbers (NOT a string that display as numbers) then you cannot use & "*" as that transforms the criteria value to a String
If a column contains Strings (even if they look as numbers) then you need to format the filtering cell (B2-C2-D2) as Text before writing characters in it (or use the character "single quote" to force a number to be a string, eg '55 will enter the string 55)
If you are unsure whether a cell is a number or a string try formatting it as number with two decimals: if it display accordingly then it's a number, otherwise it's a text)

Hope things are a little bit more clear now