Copy and insert row

**KForsman** · 04-14-2016, 10:21 AM

Hi,

I want to copy current row and insert below.
Only formulas (and eventually formatting) should be copied.
It works fine but the problem is that values are copied too.

My code is

Public Sub insertRowBelow()
ActiveCell.Offset(1).EntireRow.Insert shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
End Sub

I found another thread from 2007 with similar problem but that code is completely different and I don't know how to combine these two codes.

Part of the solution (if you don't want to read the conversation) was to add:
    If Target.Row = lastrow2 Then
        Rows(lastrow2 - 1).Copy
        Rows(lastrow2).Insert
        On Error Resume Next
        Rows(lastrow2).SpecialCells(xlCellTypeConstants).ClearContents
        On Error GoTo 0
        Application.CutCopyMode = False
    End If

Can anyone please help me to extend the code to remove values and only keep the formulas?

**Kaper** · 04-14-2016, 10:42 AM

try:

Public Sub insertRowBelow()
ActiveCell.Offset(1).EntireRow.Insert shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormulasAndNumberFormats
On Error Resume Next
ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.CutCopyMode = False
End Sub

error handling added to avoid problems if there was no constants if you are sure that there will be always some constants, you can add just this middle line

**KForsman** · 04-15-2016, 02:37 AM

Thank you Kaper, this works fine!

Just one more thing. To make my file work for other users as well without the need of the Developer bar or quick commands - is it possible in any way to run this macro by a hyperlink?

I will try to upload my spreadsheet to make it clearer what I mean. In column A I have the code ="+" (must have it as a formula so the + won't disappear with the insert row macro).
I planned this to be used as a hyperlink to the macro but I really can't find any good solution on the web.

**Kaper** · 04-15-2016, 05:09 AM

My proposition is to write event handler for doubleclick event (it normally enters cell editing mode).
so doubleclick anywhere in a sheet will do what it does normally, but if it is coubleclick in column A and clicked cell contains "+ as a formula" :-) then main macro is called.
See code of the R&D Plan sheet in attachment. it contains:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Columns("A")) Is Nothing Then
  If Target.Cells.Count = 1 Then
    If Target.Formula = "=""+""" Then
      Call insertRowBelow
      Cancel = True
    End If
  End If
End If
End Sub

**KForsman** · 04-15-2016, 07:07 AM

You made my day Kaper!

It really works! Thank's a lot!

But now I need to spread reputation to others before I can reputate you more ;-)

**Kaper** · 04-18-2016, 11:16 AM

I'm glad to hear it suits you, and thanks for the reputation. It would be also lovely if you could select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Copy and insert row

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy and insert row

Re: Copy and insert row

Re: Copy and insert row

Re: Copy and insert row

Re: Copy and insert row

Re: Copy and insert row

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Adapt copy and insert VBA code to ask how many times your would like it to copy and insert

Macro copy row-insert it lower under section, when cell filled copy-insert again lower

[SOLVED] VBA codes to insert the rows and copy the first entire row text and insert sheet

[SOLVED] Macro to copy row, insert below, invert amt, and insert blank row between each new tx

Macro: Copy Row and Insert it ; Insert Odd Year

Copy paste data, insert rows, insert data and change . to -

Macro to insert copy and insert formulas only to next blank row

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions