copy cell to next open line

[pswanie]

on sheet1 cell c15 i got ppl entering the day sales. and alot of other info.

all the other info gets cleared with a macro. mike h gave me a
threat that will "copy and paste" sheet1 cell c15 upon the enter being hit.
problem are that if the wrong sales input have been done the wrong amount
will be "carried" over. and when they go back to enter the correct data that
will then be added to the next open line on sheet2.

sheet2 looks like this and the day's sales will then be carried over to
colum d
______________________________________________________
a b c d
______________________________________________________
1 \ sales projection
2 \ date day last yr sales this yr sales
3 \ 01/07/07 mon $1.20 ****
4 \ 02/07/07 tues $1.20 ****
5 \ 03/07/07 wed $1.20 ****
6 \ 04/07/07 thur $1.20 ****
7 \ 05/07/07 frid $1.20 ****


what i think i need are some thing like this...




select sheet1 range c15
copy
select sheet2
if system date 01/07/07 then select.d3 or if system date 02/07/07 then
select.d4 or if system date 03/07/07 then select.d5 etc
paste

'and then rest of my current macro

 

[Mike H]

Hi,

This collects the value from C15 (Sheet 1) looks for todays date in Column B
od shhet 2 and when it finds it puts the data in column D. Will that do?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrange As Range
If Intersect(Target, Range("c15")) _
Is Nothing Then Exit Sub
newdata = Target.Value
lastrowcolb = Worksheets("Sheet2").Range("B65536").End(xlUp).Row
For x = 1 To lastrowcolb
If Sheets("Sheet2").Cells(x, 2).Value = Date Then
Sheets("Sheet2").Cells(x, 2).Offset(0, 2).Value = newdata
End If
Next
End Sub

Mike