Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to another sheet on each day
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to another sheet on each day
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. -- Regards, Tom Ogilvy "SD" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Auto Copy/autofill Text from sheet to sheet if meets criteria | Excel Discussion (Misc queries) | |||
How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
providing a sheet-copy event or copy CustomProperties | Excel Programming |