Inserting a row below the active cell, assign the command to a button

[SamirBhowmik]

Hello,

I am quite new to VBA. I have a file where I want to insert a row after leaving out the immediate row below the active cell, and paste the contents of the row of the active cell to the inserted row in values (as in no colours, formulas etc.). I have this below code:

Sub copy_paste()

ActiveCell.EntireRow.Copy
ActiveCell.Offset(2).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveScreenUpdate = False
End Sub

I want bring some modifications to it:

1. Assign this to a button where once clicked the button would ask me how many rows I want to insert and once i mention the number, it should insert the number of rows and paste the content of the row to those newly inserted rows.
2. Also if clicked second time, the same function should add new rows instead of replacing the existing ones.
3. Lastly, after all that the cursor or the active cell should be back to the active cell from where I initiated the process

Can anyone help me to achieve this please?

Thank you

 

[cmowla]

2. Also if clicked second time, the same function should add new rows instead of replacing the existing ones.

This part I don't understand. Where did you tell us before that we were replacing anything? Maybe if you explain this, someone can whip up something for you pretty quickly.

Also, your current sub can be reduced to:

Sub copy_paste()

ActiveCell.Offset(2, 0).EntireRow.Value = ActiveCell.EntireRow.Value

End Sub

 

[SamirBhowmik]

This part I don't understand. Where did you tell us before that we were replacing anything? Maybe if you explain this, someone can whip up something for you pretty quickly.

Also, your current sub can be reduced to:

Sub copy_paste()

ActiveCell.Offset(2, 0).EntireRow.Value = ActiveCell.EntireRow.Value

End Sub

Hello cmowla: Infact right now with the existing code, it always copies the content in the row after the immediate row from the active cell (hence Row no 2 from the starting point). and if I click again it does the same by replacing the copied content. What I want is that, it should insert a new row and copy the content and shift the earlier inserted row below it thus for ex: shifting the row no 2 to 3

I don't know if it's clearer ?

 

[cmowla]

What I want is that, it should insert a new row and copy the content and shift the earlier inserted row below it thus for ex: shifting the row no 2 to 3

I don't know if it's clearer ?

This sounds like the same effect as inserting a new row above the active one. (No copying and pasting required.)

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub SamirBhowmik()
   Dim NewRws As Variant
   
   NewRws = InputBox("How many rows do you want to insert?")
   If NewRws = "" Then Exit Sub
   ActiveCell.EntireRow.Copy
   ActiveCell.Offset(2).Resize(NewRws).EntireRow.Insert xlShiftDown
   Application.CutCopyMode = False
End Sub

 

[SamirBhowmik]

Sub SamirBhowmik() Dim NewRws As Variant NewRws = InputBox("How many rows do you want to insert?") If NewRws = "" Then Exit Sub ActiveCell.EntireRow.Copy ActiveCell.Offset(2).Resize(NewRws).EntireRow.Insert xlShiftDown Application.CutCopyMode = False End Sub

Hello Fluff,

Thanks a bunch, that really did the trick. However, in fact the entire row of the active cell has particular formatting (Background Yellow). Is there anyways I could copy te content with this format? also, for the conditional part "If NewRws = "" Then Exit Sub" can I also add that the macro should stop if the number of rows are less than 0?

 

[Fluff]

Is there anyways I could copy te content with this format?

The code already does that.
To prevent errors if anything less than 1 is input use

Sub SamirBhowmik()
   Dim NewRws As Variant
   
   NewRws = InputBox("How many rows do you want to insert?")
   If NewRws = "" Then Exit Sub
   If NewRws < 1 Then Exit Sub
   ActiveCell.EntireRow.Copy
   ActiveCell.Offset(2).Resize(NewRws).EntireRow.Insert xlShiftDown
   Application.CutCopyMode = False
End Sub

 

[SamirBhowmik]

The code already does that.
To prevent errors if anything less than 1 is input use

Sub SamirBhowmik()
   Dim NewRws As Variant
  
   NewRws = InputBox("How many rows do you want to insert?")
   If NewRws = "" Then Exit Sub
   If NewRws < 1 Then Exit Sub
   ActiveCell.EntireRow.Copy
   ActiveCell.Offset(2).Resize(NewRws).EntireRow.Insert xlShiftDown
   Application.CutCopyMode = False
End Sub

Hello Fluff,

Thanks again, Sorry I mistyped the first requirement. Actually, the row I am copying the content from is formatted and I just simply want to get the content without any formats like colours for example. Could you kindly help with that part as well?

 

[Fluff]

Ok, how about

Sub SamirBhowmik()
   Dim NewRws As Variant
   
   NewRws = InputBox("How many rows do you want to insert?")
   If NewRws = "" Then Exit Sub
   With ActiveCell
      .Offset(2).Resize(NewRws).EntireRow.Insert xlShiftDown
      .EntireRow.Copy
      .Offset(2).Resize(NewRws).PasteSpecial xlPasteValues
      .Select
   End With
   Application.CutCopyMode = False
End Sub