Copy chosen cells to another column-CodePudding

I have data set from SAP system. The data structure looks bad. Data looks like below. Each value is separated by 4 rows which contain data for other columns. What I need to do is to copy the cell and paste it to the proper column then go back and copy another value B which is 4 rows under A etc.

A



B



C

I tried to develop code but it does not work properly. Could you please look at the code and suggest me something?

Sub Create_table()

Dim R As Long
Dim R2 As Long
R = 2
R2 = 7
Range("B7").Select

Do While ActiveCell.Value <> ""
    ActiveCell.Copy
    Cells(R, 16).PasteSpecial
    Cells(R2   4, 2).Copy
    Cells(R   1, 16).PasteSpecial
    
Loop


End Sub

CodePudding user response：

If it's consistent that there are 4 lines between each record, then this approach will work with formulas.

Cell P2 = =OFFSET($B$6,(ROW()-2) * 4,0)

... and fill down.

B6 is where your first value is and the formula expects to start in the second row.

CodePudding user response：

You are not iterating the values

and all the copy paste have to be inside the while

Sub Create_table()

Dim R As Long
Dim R2 As Long
R = 2
R2 = 7

Do While ActiveCell.Value <> ""
  Cells(R2, 2).Copy
  Cells(R, 16).PasteSpecial
  R = R   1
  R2 = R2   4
Loop

End Sub