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
|
Similar Threads
