Copy row to another sheet on each day

[SD]

Hi there,

I have a spreadsheet with sheet 1 having values in cells a4, b4, and c4 ….
date being in cell a4 (=now() ).

I want to copy it each day to sheet2 with the date being in cell a1 and the
rest in cell b2, c1, d1 and the next day the data will go to a2,b2,c2, and d2.

In other words each day I want it to determine if todays information from
sheet 1 is in sheet2…if it is copy over existing data…. Or if date is
different then copy the days data in the below row. Each day the next days
data will get copied below.

Thanks very much

SD

 

[Tom Ogilvy]

Assuming you want this done whenever the workbook is opened then
Use the Workbook_Open Event. Put code like this is the ThisWorkbook Modules

Private Sub Workbook_Open()
Dim rng as Range, rng1 as Range
Dim res as Variant
With Worksheets("Sheet1")
Set rng = .Range(.Cells(1, 1), .Cells(1, 256).End(xlToLeft))
End With
With Worksheets("Sheet2")
Set rng1 = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
res = Application.Match(rng(1), rng1, 0)
If Not IsError(res) Then
rng1(res).Offset(0, 1).Resize(1, rng.Count - 1).Value = _
rng.Offset(0, 1).Resize(1, rng.Count - 1).Value
Else
rng1.Offset(rng1.Count, 0).Resize(1, rng.Count).Value = _
rng.Value
End If
End Sub

If you are not familiar with events, see Chip Pearson's page on events

http://www.cpearson.com/excel/events.htm

If another event is more appropriate, that page should give you some
insights.