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

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.

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

Sub TryNow()
Dim myFind As Integer

myFind = Worksheets("Sheet1").Range("A1").Value
Worksheets("Sheet1").Range("C499:IV499").Find(myFi nd, _
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").ClearConte nts
End Sub


--
HTH,
Bernie
MS Excel MVP


"ufo_pilot" wrote in message
...
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.

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

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(myFi nd, _
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").ClearConte nts
End Sub


--
HTH,
Bernie
MS Excel MVP


"ufo_pilot" wrote in message
...
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.