How can I copy most recent cells value added in a range from a different sheet to the active one?

Question

I am trying to write a code in sheet1 that would add recent date according to the dates in sheet2.

   Sheet1                              Sheet2

    A                                    A

50  01/02/2019                 76     01/02/2019

51  01/03/2019                 77     01/03/2019 

52  01/04/2019                 78     01/04/2019 

                               79     01/05/2019 

                               80     01/06/2019

I thought about copying the whole column and pasting it but it will be unprofessional and the data can be thousands of rows.

Instead how do I check the recent dates in sheet2 (ColumnA) that are not in the sheet1 and copy them?

This is my code so far

Sub Addrecentmonth()

Dim M1 As Worksheet
Dim FinalRowM1 As Long
Dim FinalRowPM As Long
Dim ProbabilityModel As Worksheet

Set M1 = ActiveWorkbook.Worksheets("M1")
Set PM = ActiveWorkbook.Worksheets("ProbabilityModel")

FinalRowM1 = M1.Range("A" & M1.Rows.Count).End(xlUp).Row
FinalRowPM = PM.Range("A" & M1.Rows.Count).End(xlUp).Row

If PM.Range("A" & FinalRowPM).Value <> M1.Range("A" & FinalRowM1).Value Then

    M1.Range("A" & FinalRowM1).Select

    PM.Cells(Rows.Count, "A").End(xlUp).Offset(1). _
      PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=True

    Application.CutCopyMode = False

End If

End Sub

I figured how to paste target after the last used cell, but I couldn't find a way to copy the recent dates from the other sheet.

So basically there are two columns: a ascending number and a date per row? You just want to avoid transferring rows from sheet 2 where the date already exists in Sheet 1 without any regard for the number in the first column? Or does the combination have to be unique? — Milan Pijanovic
the numbers (76,77,78...) displayed in the example are the row number reference not a cell value or a column! I only want to copy the cells that contains dates (A79, A80.... to the last cell used) — Rayan Bared
Ah okay, I'll have a look at your code first. By the way, I have just found out, you forgot to declare the "PM" variable. I guess, it should be like "M1". — Milan Pijanovic
oh yeah true, thanks for the notice. Im trying to figure it out, what If we apply smtg like: copy from the last cell used in column A to (X) and X will be declared as a range or string using LOOKUP, where the lookup formula will go to sheet 1 and find the last cell used in Column A and then address it by its value (01/04/2019). Would this work? — Rayan Bared
Hm... and in both sheets, data are filled in scending order like in your example? The newer a date the closer it is to the bottom? If so, you could check the most recent entry in your sheet1 ("01/04/2019" in your example), then go to sheet2 and select the cell, were the date is bigger than "01/04/2019", then copy everything from this cell to the bottom and paste it into sheet1. — Milan Pijanovic

Thorgaran Thorgaran · Accepted Answer · 2019-12-27T21:28:58

If dates are sorted from oldest to newest like in your example, you could select the last cell of Sheet2 where the date is smaller than Sheet1 last cell.

This code does it:

Dim currentDate As Long
Dim Scanner As Range

currentDate = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Value
'currentDate contains the value of Sheet1 last cell'
Set Scanner = Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp)
'Here, Scanner is the last cell of Sheet2'

While Scanner.Value > currentDate
    'Scanner goes upwards until it finds the current date'
    Set Scanner = Scanner.Offset(-1, 0)
Wend

You can then copy all cells that are below Scanner to the end of Sheet1.

How can I copy most recent cells value added in a range from a different sheet to the active one?

1 Answers