code to FIND value, copy, paste values onto other sheet

[ufo_pilot]

I have cells which will read values only on certain dates, after the date
changes, the values dissapear and the new values are entered. But I need to
capture the values on a daily basis ( copy - paste - values only) from one
sheet to another in the rows matching a certain value ( Julian Dates - in row
499)

First the code needs to check the (Today()) cell in A1 sheet1 ( witch is
calculated in JULIAN DATE CODES ( 1-365)
then it should :

In sheet1 - Find /or match cell in row 499 ( column c through IV) which
equals (this value- Julian date)
Then copy rows 500 to 540 under matching Julian date from this column and
paste values in sheet2 to rows 36 - 76 in matching Julian date column.

I have a button ready (send data) which I would assign the macro to .
Can I also have the data entry cells (A151 - A300) cleared after this button
has been activated - ready for the next day

How would I go about doing this?
Thank You for your help in advance.

[Bernie Deitrick]

Sub TryNow()
Dim myFind As Integer

myFind = Worksheets("Sheet1").Range("A1").Value
Worksheets("Sheet1").Range("C499:IV499").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
Resize(41, 1).Copy
Worksheets("Sheet2").Range("35:35").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
Resize(41, 1).PasteSpecial xlPasteValues
Worksheets("Sheet1").Range("A151:A300").ClearContents
End Sub


--
HTH,
Bernie
MS Excel MVP


"ufo_pilot" <[email protected]> wrote in message
news:[email protected]...
>I have cells which will read values only on certain dates, after the date
> changes, the values dissapear and the new values are entered. But I need to
> capture the values on a daily basis ( copy - paste - values only) from one
> sheet to another in the rows matching a certain value ( Julian Dates - in row
> 499)
>
> First the code needs to check the (Today()) cell in A1 sheet1 ( witch is
> calculated in JULIAN DATE CODES ( 1-365)
> then it should :
>
> In sheet1 - Find /or match cell in row 499 ( column c through IV) which
> equals (this value- Julian date)
> Then copy rows 500 to 540 under matching Julian date from this column and
> paste values in sheet2 to rows 36 - 76 in matching Julian date column.
>
> I have a button ready (send data) which I would assign the macro to .
> Can I also have the data entry cells (A151 - A300) cleared after this button
> has been activated - ready for the next day
>
> How would I go about doing this?
> Thank You for your help in advance.
>
>
>

[ufo_pilot]

Bernie, thank you for the reply.
I copied and pasted it into a module, changed some of the sheet names,
assigned it to the "send" button, clicked it and got this:

Run time error '91'
Object variable or With block variable not set

this part of the code is highlighted:

Worksheets("Sheet2").Range("35:35").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
Resize(41, 1).PasteSpecial xlPasteValues


I have renamed Sheet2 "January" and Sheet1 " INPUT" , because that's what
they are called now.
Does it matter that I have all but the INPUT sheets hidden and they can only
be viewed upon a click-button which is on the first sheet?

Any help is greatly appreciated.
Thank You



"Bernie Deitrick" wrote:

> Sub TryNow()
> Dim myFind As Integer
>
> myFind = Worksheets("Sheet1").Range("A1").Value
> Worksheets("Sheet1").Range("C499:IV499").Find(myFind, _
> LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
> Resize(41, 1).Copy
> Worksheets("Sheet2").Range("35:35").Find(myFind, _
> LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0). _
> Resize(41, 1).PasteSpecial xlPasteValues
> Worksheets("Sheet1").Range("A151:A300").ClearContents
> End Sub
>
>
> --
> HTH,
> Bernie
> MS Excel MVP
>
>
> "ufo_pilot" <[email protected]> wrote in message
> news:[email protected]...
> >I have cells which will read values only on certain dates, after the date
> > changes, the values dissapear and the new values are entered. But I need to
> > capture the values on a daily basis ( copy - paste - values only) from one
> > sheet to another in the rows matching a certain value ( Julian Dates - in row
> > 499)
> >
> > First the code needs to check the (Today()) cell in A1 sheet1 ( witch is
> > calculated in JULIAN DATE CODES ( 1-365)
> > then it should :
> >
> > In sheet1 - Find /or match cell in row 499 ( column c through IV) which
> > equals (this value- Julian date)
> > Then copy rows 500 to 540 under matching Julian date from this column and
> > paste values in sheet2 to rows 36 - 76 in matching Julian date column.
> >
> > I have a button ready (send data) which I would assign the macro to .
> > Can I also have the data entry cells (A151 - A300) cleared after this button
> > has been activated - ready for the next day
> >
> > How would I go about doing this?
> > Thank You for your help in advance.
> >
> >
> >
>
>
>