[VBA] Copy/Paste different values based on date

Yooks

New Member
Joined
Apr 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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)​

EXCEL_RDLK7aTy8K.png


1617404573984.png
 

Yooks

New Member
Joined
Apr 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
Solution

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,147
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Seems like you solved most of it yourself anyway....well done... (y) (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,680
Messages
5,637,749
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top