Ask a question »

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

April 2015


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.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-copy-data-next-to-a-cell-and-paste-it-below.pdf

See also

In the same category

Published by aakai1056. - Latest update by deri58
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.