Copy and Paste data by date - Excel VBA

Question

I need to move some data from one sheet to the other according to the date, but the sealing I made using the IF command only selects the last cell that reaches this criterion ignoring the others.

What I got so far:

Sub Copiar()

    Dim range1 As Range

    Set range1 = Range("k56:k58")

    For Each cell In range1
        If cell.Value = Range("R55").Value Then
            cell.Offset(0, 2).Select
            Selection.Copy
            Sheets("Plan2").Activate
            Range("r56").Select
            ActiveSheet.Paste
        End If
    Next
End Sub

Answer 1

This is because all the cells that meet the criteria are glued together in the same place, so the last occurrence is the view. Go through the code step-by-step with F8 and you’ll realize that it occurs.

Code

In order not to occur, the value of the line that must be pasted must be incremented.

Sub Copiar()

    Dim range1 As Range
    Dim i As Long

    Set range1 = Range("k56:k58")

    For Each cell In range1
        If cell.Value = Range("R55").Value Then
            cell.Offset(0, 2).Select
            Selection.Copy
            Sheets("Plan2").Activate
            Range("r" & 56 + i).Select
            Selection.Paste
            i = i + 1
        End If
    Next
End Sub

Note: Try to avoid using .Select/. Activate/Selection/Activecell/Activesheet/Activeworkbook.

The code would stand:

Sub Copiar()

    Dim range1 As Range
    Dim i As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Plan2")
    Set range1 = ThisWorkbook.Sheets("Plan1").Range("k56:k58")

    For Each cell In range1
        If cell.Value = ws.Range("R55").Value Then
            cell.Offset(0, 2).Copy ws.Range("R" & 56 + i)
            i = i + 1
        End If
    Next
End Sub