0
Thanks

A few words of thanks would be greatly appreciated.

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.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

Published by . Latest update on by deri58.

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 (https://ccm.net/).

0 Comments