Error 1004

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
503
I have a simple macro and I want to copy the row above the active cell row and use that as my template. I then want to insert that row above the active cell row. When I run my code below, I get the Error 1004 Insert method of Range Class Failed. When I select debug, the following line is highlighted.

ActiveSheet.Rows(ActiveCell.Row).EntireRow.Insert Shift:=xlDown, CopyOrigin:=TempRow


I would then like to clear out the cells of the newly entered row from column A, B, and E. I have that code but I do not know how to refer to the new row that has been inserted. Would the TempRow +1 work?


VBA Code:
Sub ROW_INSERT_1()

Dim TempRow As Range

Set TempRow = ActiveSheet.Rows(ActiveCell.Offset(-1, 0).Row).EntireRow
TempRow.Copy

ActiveSheet.Rows(ActiveCell.Row).EntireRow.Insert Shift:=xlDown, CopyOrigin:=TempRow

'    Clear cells code here
Range("A" & TempRow + 1).ClearContents
Range("B" & TempRow + 1).ClearContents
Range("E" & TempRow + 1).ClearContents

End Sub

I also want to create a button to add 5 rows. I have written the following code, but I am afraid I would get the same error.

Code:
Sub ROW_INSERT_5()

Application.ScreenUpdating = False

Dim TempRow As Range

Set TempRow = ActiveSheet.Rows(ActiveCell.Offset(-1, 0).Row).EntireRow

n = 0

Do Until n = 5
    TempRow.EntireRow.Copy
    ActiveSheet.Rows(ActiveCell.Row).Offset(-1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=TempRow
    Range("A" & TempRow + 1).ClearContents
    Range("B" & TempRow + 1).ClearContents
    Range("E" & TempRow + 1).ClearContents
    n = n + 1
Loop

Application.ScreenUpdating = True

End Sub

I appreciate any insights on how to get this fixed.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
TempRow is a Range object, copy origin should be an integer so it is invalid.

Same for the 3 lines at the end, you need to use temprow.Row to avoid errors there.

Also, it would be preferable to copy and insert only the used columns rather than the entire row.
 
Last edited:

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
503
So how do I modify the copy or insert lines so that they work. I have reviewed the link you provided and I am still having no success. There are formulas in the template row so I need to copy those from the template row and insert a row with all of the copied formulas.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
You would need to use
VBA Code:
ActiveSheet.Rows(ActiveCell.Row).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Although I don't think that you will actually need the last part for what you are doing, as far as I'm aware that is just to copy cell formats when you insert without copying first, with copied cells it should include everything from the source by default.
 

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
503
I still get the same error, even after copying the line indicated above into the macro. What am I missing?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
You will get an error if row 1 is selected as there is nothing above it to copy.
Sheet protection may be preventing it from working, if you have protected the sheet you will need to unprotect it as part of the procedure.
It is possible that it will not work with merged cells it there are any in the affected rows, they tend to break most things.

Other than that I can see no cause for error with that line, it works fine for me. Although, as I pointed out in my first reply, there are errors waiting in other lines of the code if you did not yet correct them.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,730
Messages
5,626,559
Members
416,190
Latest member
plee3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top