Adevance filter using VBA not show the requested data
 

Hello,

This is my VBA to filter data from one sheet and copy to another sheet. The
intention is to filter Job No in Sheet "Data", and will be copy the filterred
one into Sheet "JobNo.". But if the Job number M type in the filter criteria
it will show all the Job code starting with M. My question how can I make
only Job M shows up, because Job M is the job which has not been distributed
yet to actual Job. No. And we need them to be reminded to the project Mnager.

Should we Dim something as bolean or something like that?

These are my VBA's
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

I appreciate any idea provided.

Frank

Adevance filter using VBA not show the requested data
 

Sheets("data").Range("criteria2")(2,1).Formula = "=""=M"""

If you were manually doing it, this is what you would enter in the criteria
cell

="=M"

and it would display =M


Tested and it worked fine for me.
--
Regards,
Tom Ogilvy


"Frank Situmorang" wrote:

Hello,

This is my VBA to filter data from one sheet and copy to another sheet. The
intention is to filter Job No in Sheet "Data", and will be copy the filterred
one into Sheet "JobNo.". But if the Job number M type in the filter criteria
it will show all the Job code starting with M. My question how can I make
only Job M shows up, because Job M is the job which has not been distributed
yet to actual Job. No. And we need them to be reminded to the project Mnager.

Should we Dim something as bolean or something like that?

These are my VBA's
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

I appreciate any idea provided.

Frank

Adevance filter using VBA not show the requested data
 

Thanks Tom for your reply, but maybe I want to tell you more about my problems

This is my layout of the sheet named "data":

Dated Issued Client name JobNo. Amount m Prj.Mgr
--------------- ----------- ---------- --------- --------
11/7/07 CompanyA M10-7-006 10,000,- Emura
10/5/07 Cmpany B M 20,000,- Maryama
09/05/07 Coy C E 30,000,- Nagasaki
....goes down to 15,000 lines

In another Sheet named "JobNo.":
Dated Issued Client name JobNo. Amount m Prj.Mgr
--------------- ----------- ---------- --------- --------
the VBA will filter then copy it to this sheet.

What I want is to copy the Job number M only to this sheet, but it always
copy all job starts with M, while I want is M only, because if it is M or E,
means the project Mgr has not assigned yet to which prjoect number it will be
coded, so we need to remind them from Finance.

My Question is why it can not pick up only the JOb no. M or E. Should I
modify the VBA?

Thanks for any help provided.

Frank





"Tom Ogilvy" wrote:

Sheets("data").Range("criteria2")(2,1).Formula = "=""=M"""

If you were manually doing it, this is what you would enter in the criteria
cell

="=M"

and it would display =M


Tested and it worked fine for me.
--
Regards,
Tom Ogilvy


"Frank Situmorang" wrote:

Hello,

This is my VBA to filter data from one sheet and copy to another sheet. The
intention is to filter Job No in Sheet "Data", and will be copy the filterred
one into Sheet "JobNo.". But if the Job number M type in the filter criteria
it will show all the Job code starting with M. My question how can I make
only Job M shows up, because Job M is the job which has not been distributed
yet to actual Job. No. And we need them to be reminded to the project Mnager.

Should we Dim something as bolean or something like that?

These are my VBA's
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

I appreciate any idea provided.

Frank