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!

 

[Fluff]

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

 

[S42k20]

Wow !! Thank you very much!! It works!

One thing I added to avoid the selection of the row is

Application.CutCopyMode = False

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

would you approve this as clean code?
Thanks again and best regards!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[S42k20]

You're welcome & thanks for the feedback.

Thank you!
So would you say that it is fine to add this line like I did?

Application.CutCopyMode = False

Cheers

 

[Fluff]

Yes that's fine

 

[S42k20]

Sorry to re-open this topic
I observed a different behaviour with dropdown list fields in cells:

When there is a dropdown list but with no value selected, it is copied fine.
If there was a value selected before, the dropdown is not being copied at all.

Is there a chance to copy it but as an empty one?

Thank you

 

[Fluff]

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

 

[S42k20]

Perfect !! Thanks a lot for your help!
I don't really understand it (how formulas and dropdowns are copied) but I am happy that it is working

 

[Fluff]

Glad to help & thanks for the feedback.