Insert rows below and place the text from above cells

[Panoos64]

Hi all, kindly require to provide me a VBA code so that to insert rows below and place exactly the same contents (e.g. numbers, dates, descriptions e.t.c.) as from above rows cells, except the contents in col. "E" and "J". Thank you all in advance

 

[Joe4]

Try this:

Sub MySpecialCopyMacro()

    Dim r As Long
    
'   Capture active row
    r = ActiveCell.Row
    
'   Insert new row
    Rows(r).Copy
    Rows(r + 1).Insert Shift:=xlDown
    Application.CutCopyMode = False
    
'   Clear columns E and J
    Cells(r + 1, "E").ClearContents
    Cells(r + 1, "J").ClearContents
    
End Sub

Note that you can get a lot of this code by using the Macro Recorder.
Don't be afraid to mess around with it. It is a great tool to assist you in learning VBA!

 

[Panoos64]

Hi Joe, It doesn't work but it would much appreciate if you could advice me how to record such commands. I mark the range of rows & columns which i wanted to insert the rows but i do not know then the next step or function. Please note that the range is A2:K... till last fulfill row. However thank you for your support. Have a great day!

 

[Joe4]

If you turn on the Macro Recorder, and record yourself performing the steps manually, it will give you the code that performs those actions.
It is very literal, so it will reference your exact range references, so it will often require generalizing it to make it more dynamic, and the code can often use a little "celan-up", but it will usually give you a lot of the code you need to get started.

Please explain in more detail exactly HOW you want to use this. In your last post, you said:

I mark the range of rows & columns which i wanted to insert the rows

which you did not mention originally, but you do not mention exactly how you are marking these rows.

 

[Panoos64]

Hi again Joe, i drag the cursor from "A2:K1000" and so i mark the range which i want to insert multiple rows and then "right click" then "Insert Rows" but it appears the rows above. I need to insert rows below each one and copying any texts, values, descriptions which contains the above row. Is there a way to achieve it using excel menu? Thank you so much!

 

[Joe4]

No, if you want it to do what you want, you will not select "Insert Rows", you will run the VBA code instead. The standard options on the Excel menu will not do what you want, you need VBA to do that.

The original code I wrote above works on a single row. Whatever row your cursor is in is the row it will apply it to (insert the row below that active row).

If you want it to work on the entire selected range, try this version instead:

Sub MySpecialCopyMacro()

    Dim fRow As Long, lRow As Long
    Dim fCol As Long, lCol As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find first and last row and columns in range
    fRow = Selection(1).Row
    fCol = Selection(1).Column
    lRow = Selection.Cells(Selection.Rows.Count, Selection.Columns.Count).Row
    lCol = Selection.Cells(Selection.Rows.Count, Selection.Columns.Count).Column
   
'   Loop through all rows going backwards
    For r = lRow To fRow Step -1
'       Insert new row
        Rows(r + 1).Insert Shift:=xlDown
        Range(Cells(r, fCol), Cells(r, lCol)).Copy Cells(r + 1, fCol)
        Application.CutCopyMode = False
'       Clear columns E and J
        Cells(r + 1, "E").ClearContents
        Cells(r + 1, "J").ClearContents
    Next r
   
    Application.ScreenUpdating = True
    
End Sub

So, to be clear, in order to run it, simply select the range you want to apply it to and run the Macro.
If you want to run it from a Menu, you can add a custom macro button to one of your menus and assign this macro to it, or you can assign it to keyboard shortcut or a button you put on your worksheet.

 

[Panoos64]

Is perfect now Joe, a great thanks for your support and thanks also for your advice in such commands. Have a lovely day!!

 

[Joe4]

You are welcome.