Hi Frank,

The range "Criteria2" is where the filtering query information resides.
Within that range you should have the crieteria "M*" in the column.
That will return all records starting with M. Hope that works.

Aran Black

"Frank Situmorang" wrote:

Hello,

This is the VBA of filtering a data and then copy it to another sheet. This
code I found from this very helful forum. My question if I filter it for M
Code only( coz the project code is M10-7-001 or E10-7-005 and so on) it show
all, not only M. Anyone can help me why?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 5 And Target.Column = 4 Then
'calculate criteria cell in case calculation mode is manual
Sheets("data").Range("Criteria2").Calculate
Worksheets("data").Range("database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("data").Range("criteria2"), _
CopyToRange:=Range("B10:v10"), Unique:=False
End If
End Sub


Thanks very much

Frank