[VBA] Copy/Paste different values based on date

[Yooks]

Hi peeps,

I want a VBA that will automatically copy/paste data from sheet1 to sheet2.​

It should copy the Oracle value(D4:D8), Name(E4:E8), Code(F4:F8) and the date of the day (F2).​

​

​

So in example below. I have 4 agents, they were all present on April 2nd.​

I now want to move all that data onto sheet2 in the sheet below.​

​

Now when we get to tomorrow I want it to copy the same but for April 3rd (G2).​

It should not overwrite any additional data. It should not copy the data if the code cell is blank (F4:F8)​

 

[Yooks]

Messed around and came up with the solution below. Thank you so much Michael for the base code. This helped me mess around and find away to make it work how I need it too.

Sub MM1()
 Dim lr As Long, lr2 As Long, r As Long, ws1 As Worksheet, ws2 As Worksheet
 Dim lc As Integer
 Set ws1 = Sheets("Sheet1")
 Set ws2 = Sheets("Sheet2")
 lc = Evaluate("MATCH(TODAY(),2:2,0)")
 lr = ws1.Cells(Rows.Count, "E").End(xlUp).Row
 lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
 With ws1
     For r = 4 To lr
        If .Cells(r, lc).Value <> "" Then
            .Range(Cells(r, 4), Cells(r, 5)).Copy
            ws2.Cells(lr2 + 1, 1).PasteSpecial xlPasteValues
            ws2.Cells(lr2 + 1, 3).Value = .Cells(r, lc)
            ws2.Cells(lr2 + 1, 4).Value = Evaluate("Today()")
            lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
        End If
    Next r
End With
End Sub

 

[Michael M]

Seems like you solved most of it yourself anyway....well done...