CODEWITHSUNDEEP
excelvba
Hubert S
Hubert S

Reputation: 77

Copy chosen cells to another column

enter image description hereI 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

Upvotes: 0

Views: 57

Answers (2)

Skin
Skin

Reputation: 11197

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.

Upvotes: 1

Luis Curado
Luis Curado

Reputation: 756

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

Upvotes: 0

Related Questions