Rob,

Give this a try. It assumes that the dates are in A2, A3, etc,. so
calculates the row number based upon this.

Sub Test()
Dim cLastRow As Long
Dim cLastCol As Long
Dim thisDate As Date
Dim i As Long

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To cLastRow
Worksheets("Sheet2").Cells(1, i - 1).Value = _
Cells(i, "A").Value
Next i
thisDate = Range("A1").Value

With Worksheets("Sheet2")
.Range("A1").Resize(1, i - 1).Copy _
Destination:=.Cells(thisDate - DateSerial(Year(thisDate), 1, 0)
+ 1, "B")
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob Gould" <robin_gould_durban@hotmail.com> wrote in message
news:1110186929.277562.122520@z14g2000cwz.googlegroups.com...
> Hi all,
>
> I am trying to get a macro to work in Excel. Basically, this is what I
> want it to do:
>
> A user will enter a date into a cell, say A1 on Sheet 1. They then
> enter some sales figures in cells below this. These figures are picked
> up on Sheet 2 in Row 1 so that they are all in a row. Below this row I
> have all the days of the year. Once the user has finished entering the
> figures, the macro needs to copy the row of data on Sheet 2 and paste
> Values Only to the row corresponding to the date entered. This way, I
> am building up a year of data. I don't want users entering data
> directly onto Sheet 2.
>
> Doing this manually, I copy the date entered on Sheet 1, go to Sheet 2,
> CTRL+F to Find, paste the date into the find what box, find the cell
> with the date, close the find box, move 1 cell to the right and enter
> "qq" (could be anything really). Then I copy the row of data in
> row A of Sheet 2, Find "qq" and paste values only.
>
> When I incorporate this into a macro, however, it stops during the find
> the date section of the process.
>
> Question 1: Is there a far better way of doing this?
> Question 2: If not, how do I solve the problem?
>
> This looks pretty confusing, I'm sure, but if you have the time to
> try to follow it, I would appreciate the help!
>
> Regards,
>
> Rob
>