Thread: Copy data from one set of cells to another cells and repeat the process -sheet attach

Hi I am fairly new to VBA. I have a sheet which has a data from column A10 to M67. Now I want this data to be copied to column Q9 (so Q9 should show data from cell A10) , after this I would like to leave 2 rows gap below and go to cell Q12 and get the data from A11 and repeat it till the columns A is blank.
There has to be some simple way with either the formulas or macros but was not able to solve it.

This means fill data on Q9 from A10, then go to Q12 and get data from A11.
Q9=A10
Q12=A11
Q15=A12
Q18=A13

I have attached the spreadsheet and shown the example.

Originally Posted by talhawahab

hi again...I have one small problem again...can you replace cell U10,U13,U16 (all second line) with one fix account number which would be in cell (P1).

hi, please check attachment, field "Job code" will remain empty as requested

hi, talhawahab
1. As far as I understand it's a separate task though it can be incorporated in "copy_data" (please advise if so). Sample of possible separate code:

Sub test()
Dim a, i As Long, k As Long
With Range([q9], Cells(Rows.Count, "q").End(xlUp).Offset(, 8)): a = .Value
For i = 1 To UBound(a) Step 3
    If Left(a(i, 5), 1) = 1 Then
        Do
        k = k + 1: a(i + 1, k) = ""
        Loop Until k = 9: k = 0
    End If
Next: Application.ScreenUpdating = False: .Value = a: Application.ScreenUpdating = True
End With: End Sub

2. I've amended the code "copy data" not to create those "empty" strings with account number from P1

Sub copy_data()
Dim a, b, n As Long, i As Long, m As Long, zctrl As Integer, SecondLineAcc As String
Application.ScreenUpdating = False: SecondLineAcc = [p1]
a = Range([a10], Cells(Rows.Count, "a").End(xlUp).Offset(, 10)): ReDim b(1 To UBound(a) * 3, 1 To 9): n = 1
For i = 1 To UBound(a)
    zctrl = IIf(a(i, 6) <> "", 1, 0)
    For m = 1 To UBound(b, 2) - 1
        b(n, m) = IIf(IsEmpty(a(i, m)), "", a(i, m)): b(n + 1, m) = IIf(IsEmpty(a(i, m)), "", a(i, m))
    Next
    b(n, 9) = IIf(IsEmpty(a(i, 9)), a(i, 11) & "-" & a(i, 4), a(i, 9)): b(n + 1, 9) = IIf(IsEmpty(a(i, 9)), a(i, 11) & "-" & a(i, 4), a(i, 9))
    If zctrl = 1 Then
        b(n + 1, 7) = a(i, 6): b(n + 1, 6) = ""
    Else
        b(n + 1, 6) = a(i, 7): b(n + 1, 7) = ""
    End If
    b(n + 1, 5) = SecondLineAcc: b(n + 1, 8) = "": n = n + 3
Next: [q9].Resize(UBound(b), 9) = b
Application.ScreenUpdating = True: End Sub

If any outstanding issues or questions, please shout, best regards