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

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.

See also

Knowledge communities.

• Forum
• Excel forum
• Office software forum
• Programming forum

This document entitled « Excel - Copy data next to a cell and paste it below » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.