rjplante
Well-known Member
- Joined
- Oct 31, 2008
- Messages
- 569
- Office Version
- 365
- Platform
- Windows
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?
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.
I appreciate any insights on how to get this fixed.
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.