VBA: Insert row + keep formulas

[S42k20]

Hi all,

I already read some threads in this big forum but didn't find what I am looking for. I hope it is ok to ask for some help on this:

I have a table and a button to click in A1.
If I click this button, a new row shall be inserted below the current active cell including only the above used formulas in every cell of the above row. The values should not be copied.

What I had before is:

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.EntireRow.Offset(1).SpecialCells(xlCellTypeConstants).Clear

But an error occures, when the active cell is then in one of the new created cells "Runtime Error 1004 - No cells found" , which makes sense but what is the clue here ?

Thanks a lot best regards!

 

[Gtasios4]

Hi All,

I've used your very useful VBA code below but it doesn't work when I am protecting the worksheet. Any idea on how to solve this?

With ActiveCell.EntireRow
.Copy
.Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
On Error Resume Next
.Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
Application.CutCopyMode = False
On Error GoTo 0
End With

I've also added the below attached macro code in my sheet to delete rows, but the same problem appears when the worksheet is protected. Could you please advise how to solve it?

Sub DeleteEntireRow()

Selection.EntireRow.Delete

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Please start a new thread for this question. Thanks

 

[Gtasios4]

How about

   With ActiveCell.EntireRow
      .Copy
      .Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
       On Error Resume Next
      .Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
      Application.CutCopyMode = False
      On Error GoTo 0
   End With

Hi Fluff,

Sorry for opening again that topic. I've used your code that works perfectly in my excel sheet as well!

I just have a question. How can I modify the above code to insert copied rows above the "total bar"?

Something like?

For i = 5 To lastrow
If inarr(i, 1) <> "TOTAL:" Then ?

 

[Fluff]

This is a totally different question, so once again needs a new thread.

 

[Gtasios4]

This is a totally different question, so once again needs a new thread.

Thank you Fluff. Just posted one VBA: Insert copied rows

 

[gtd526]

Hi & welcome to MrExcel.
How about

   With ActiveCell.EntireRow
      .Copy
      .Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
      On Error Resume Next
      .Offset(1).SpecialCells(xlCellTypeConstants).Clear
      On Error GoTo 0
   End With

Hello,
Using your exact code above, how can you shift down 5 rows while keeping the formula?
Thank you.

 

[Fluff]

Please see post#12.

 

[eddorena]

Hi Fluff, hope you are doing well and healthily.

Sorry to jump in on this thread, I think this is related to my cases.

I have VBA code below to insert and copy the formula above line, however, I feel still not working smoothly.

Private Sub CommandButton1_Click()

On Error Resume Next
Dim iRow As Long
Dim iCount As Long
Dim i As Long

iCount = InputBox _
(Prompt:="Fill number line to insert")
iRow = InputBox _
(Prompt:="Choose the start line")

For i = 1 To iCount
    ThisWorkbook.Sheets("Sheet2").Rows(iRow).EntireRow.Insert
    ThisWorkbook.Sheets("Sheet2").Rows(Selection.Row - 1).Copy
    ThisWorkbook.Sheets("Sheet1").Rows(iRow).EntireRow.Insert
    ThisWorkbook.Sheets("Sheet1").Rows(Selection.Row - 1).Copy
Next i
End Sub

My expectation is, after click the button I fill the number of lines that need to insert then select the start line, however, the result it is not in accordance with expectation

Between Sheet1 and Sheet2 not same result, example :
I insert 5 lines, and start from line 3, in Sheet2 the result only 4 line ( 5 include the line2) and Sheet1 the result is correct.

Please your advise @Fluff, I Attached the file Sample

Thank You and Have a Nice Weekend

 

[Fluff]

This has nothing to do with the original question. So as mentioned previously please start a thread of your own.

 

[eddorena]

This has nothing to do with the original question. So as mentioned previously please start a thread of your own.

Can I use my old thread that same thing and may be different name?

VBA Macro to auto insert row into Summary sheet from other sheet as source