VBA Loop to select then copy a range of cells based on value in column B

Question

I want a macro to loop over column B and copy the cells range from cell A of the first row to cell B of the last row for references that have the same first four numbers. I have references in column B.

i.e

Column A	Column B
13/01/23	0501765
14/01/23	0501766
13/01/23	0502023
15/01/23	0512301

In this case, the code would first copy the range A1:B2, which I would then instruct it to paste (I know how to do this), after which it would repeat the loop and copy the range A3:B3, and so on.

Can someone help? I just want to know what the syntax would be for something like this.

I don’t know much about loops, here’s what I’ve come up with so far.

Dim WorkRng as Range

Set WorkRng = Range(“B:B”)

For i = 1 To WorkRng.Rows.Count
    If Left(WorkRng.Cells(i, 2).Value, 4) <> Left(WorkRng.Cells(i + 1, 2).Value, 4)

Answer 1

Try this:

Sub Macro2()

    Dim rg As Range
    Dim i As Integer
    Dim x As Integer
    Dim FirstThisRow As Integer
    Dim FirstNextRow As Integer
    Dim count As Integer

    Set rg = [c5].CurrentRegion
    On Error GoTo 1
    count = 0
    i = rg.Rows.count - 1

    For x = 1 To i
    FirstThisRow = Left(rg.Cells(x + 1, 2), 4)
    FirstNextRow = Left(rg.Cells(x + 2, 2), 4)

    If FirstThisRow = FirstNextRow Then
    count = count + 1
    Else
    1:
    Range(rg.Cells(x + 1, 1), rg.Cells(x + 1 - count, 2)).Copy
    Range("K4").Offset(x - count, 0).Select
    ActiveSheet.Paste
    count = 0

    End If
    Next x
    End Sub