If your date format is 15/03/2007 the it is either "d/mm/yyyy" or "dd/mm/yyyy".
I am assuming that since there is an 03 for the month that you also use the
01 thru 09 or the day, so change this line:
myDate = Format(myDate, "m/d/yyyy")
to this:
myDate = Format(myDate, "dd/mm/yyyy")
If that don't work use:
myDate = Format(myDate, "d/mm/yyyy")
As to the command button. The code I posted was to answer your immediate
question. You can modify the code to adapt it for use with a control click
event. The lastRow variable will have to include the 'Worksheets("Daily
Reading Master Log")' object and will have to be set as an object variable so
the search and copy will work right. Why don't you try it and if you can't
get it to work do a new posting for help with the control code.
"Carlee" wrote:
> Hi there,
>
> 1. my date format is as follows: 15/03/2007
> 2. I would like the user to be able to click a button on the 'Report
> Options' form; however, it appears that this code will only work if the
> worksheet is active. Can this be changes?
>
> Many thanks in advance,
> --
> Carlee
>
>
> "JLGWhiz" wrote:
>
> > If your date format in column B is not m/d/yyyy then the code will fail. You
> > will need to change this line in the code I gave you:
> >
> > myDate = Format(myDate, "m/d/yyyy")
> >
> > To reflect the date format that you are using. If you have a problem, just
> > post a sample of the date format you are using and I will give you a new line
> > to replace this one.
> >
> > "Carlee" wrote:
> >
> > > Hi
> > > My date column is column B, so I changed the code to the following, but
> > > nothing is happening. Worksheet 1 = 'Daily Reading Master Log' and Worksheet
> > > 2 = 'Customer1 Daily'. What am I doing wrong?
> > >
> > > lastRow = Cells(Rows.Count, 1).End(xlUp).Row
> > > myDate = InputBox("Enter a date", "User Date")
> > > myDate = Format(myDate, "m/d/yyyy")
> > > With Worksheets("Daily Reading Master Log").Range("$B$1:B" & lastRow)
> > > Set c = .Find(myDate, After:=Range("B" & lastRow), LookIn:=xlValues)
> > > If Not c Is Nothing Then
> > > pRng = c.Address
> > > .Range(pRng).EntireRow.Copy Worksheets("Customer1
> > > Daily").Range("$A$50")
> > > End If
> > > End With
> > >
> > > --
> > > Carlee
> > >
> > >
> > > "JLGWhiz" wrote:
> > >
> > > > Assuming the dates are in Column A of sheet one and sheet one is the active
> > > > sheet:
> > > >
> > > > Sub cpynPst()
> > > > lastRow = Cells(Rows.Count, 1).End(xlUp).Row
> > > > myDate = InputBox("Enter a date", "User Date")
> > > > myDate = Format(myDate, "m/d/yyyy")
> > > > With Worksheets(1).Range("$A$1:A" & lastRow)
> > > > Set c = .Find(myDate, After:=Range("A" & lastRow), LookIn:=xlValues)
> > > > If Not c Is Nothing Then
> > > > pRng = c.Address
> > > > .Range(pRng).EntireRow.Copy Worksheets(2).Range("$A$50")
> > > > End If
> > > > End With
> > > > End Sub
> > > >
> > > > The only problem with this, Carlee, is that the user must use conventional
> > > > date formats for it to be converted to the default m/d/yyyy format.
> > > >
> > > > "Carlee" wrote:
> > > >
> > > > > Hi there,
> > > > >
> > > > > I would like to copy and paste a specific row from Sheet 1, to row 50 of
> > > > > Sheet 2, based on a date provided by the user in an input box. Each date
> > > > > represents a specific row in Sheet 1.
> > > > >
> > > > > Can any one help me?
> > > > > --
> > > > > Carlee
|
Similar Threads
