# Excel - Copy data next to a cell and paste it below

October 2017

## Issue

I have data arranged as below:
```cell | A | B | C
----------------------------------------------------
1 | Hi | Hello | Morning
2 | Help | Please |
3 | Thanks | See you | Bye ```

i need to make this data to become this format

```cell | A | B | C
---------------------------------------------------
1 | Hi | Hello | Morning
2 | | Morning |
----------------------------------------------
3 | Help | Please |
----------------------------------------------
4 | Thanks | See you | Bye
5 | | Bye |
---------------------------------------------- ```

I realize that some conditions need to take into account:

To check whether C1 contains data or not,
• 1) if yes, copy the data into cell B2 (a cell relatively left and below C1)

Then underline continuously below cell B2
• 2) if no, the underline and continue with the next row
• 3) continue until the last cell that contains data

## Solution

• First copy the data in sheet2 .
• Then try the macro "test"

```Sub test()
Dim j As Integer, k As Integer
Worksheets("sheet1").Activate
j = Range("A1").End(xlDown).Row
For k = j To 1 Step -1
If Cells(k, "C") <> "" Then
If k = 1 Then
Cells(k + 1, "A").EntireRow.Insert
Cells(k, "c").Cut Cells(k + 1, "B")
Cells(k + 2, "A").EntireRow.FormulaArray = "'-----------------"
Exit Sub
End If
Cells(k, "A").EntireRow.Insert

Cells(k + 1, "C").Cut Cells(k + 2, "B")
Cells(k + 3, "A").EntireRow.FormulaArray = "'-----------------"

Else
Cells(k, "A").EntireRow.Insert
Cells(k + 2, "a").EntireRow.FormulaArray = "'-----------------"

End If
Next k

End Sub

Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
End Sub ```

## Note that

Thanks to venkat1926 for this tip on the forum.

## Related

Published by aakai1056. Latest update on September 21, 2012 at 06:39 AM by deri58.