filtering he data is not always correct
 

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

filtering he data is not always correct
 

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

filtering he data is not always correct
 

Yes..Aran, it works for my code starts with M, but actually there is a code
Only M, means that invoice was not yet split by Project Manager with the Code
number, so we want to remind them to split it, and we want to populate all
that invoices, but it doest not work if I put in cireteria2 the M.

Do you know why?

Frank


"Aran Black" wrote:

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