excel-vba – Copy and Paste data by date – Excel VBA

Question:

I need to move some data from one worksheet to another according to the date, but the selection I made using the IF command only selects the last cell that meets this criterion ignoring the others.

What do I have 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:

This is because all cells that meet the criteria are pasted in the same place, so the last occurrence is the view. Go through the code step-by-step with F8 and you'll notice that this happens.

Code

In order for it not to occur, the value of the line to 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 be:

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