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

March 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.
This document, titled "Excel - Copy data next to a cell and paste it below," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).