Copy morning and evening data and pasting in another sheet

[nilan]

In Sheet1 ColA contains today's date and ColB contains morning. ColB of the next row on the same sheet contains evening.

Now what I want is that in another sheet the macro will copy and paste the data in today's date in the morning row and in the evening row. Can any body help me with the coding please?

Thanks
Nilan

[venkat1926]

let us suppose the data is like this in sheet1 of attached file.nilan.xls




try this macro and see sheet 2

Sub test()
Dim r As Range, c As Range, r1 As Range, dest As Range
Worksheets("sheet2").Cells.Clear
Worksheets("sheet1").Activate
Set r = Range(Range("a2"), Range("A2").End(xlDown))
For Each c In r
Set r1 = Range(c, c.End(xlToRight))
Set dest = Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
r1.Copy
dest.PasteSpecial Transpose:=True
Next c
Application.CutCopyMode = False
End Sub

[davesexcel]

What have you got so far?

[nilan]

Thank you so much for the reply.

I want the data to be copied in the format as it is given in the attached file.

Sheet 1 will contain blank cells in between the numbers like this and this blank cells will contain formula in sheet 2. The data will change everyday in morning and evening in sheet1in the same row. SO the macro will be run twice, once in the morning and again in the evening. And in sheet 2 it will copy by seeing today's date and recognising it as morning or evening.

Hope I am clear in what I have said.

Thank you again for your prompt reply.

Nilan