VBA Code to copy multiple rows from one sheet and insert them below activecell in another sheet

[aitorsol]

Hi
I have an error -2147417848 of Automation on line 70 when i use this macro, sometimes the first execution, and always the second:


Public Sub InsertCopiedCells()
10 On Error GoTo InsertCopiedCells_Error
'IT IS NECESSARY TO CALL THIS MACRO FROM THE SHEET "TARGET"
'WITH A ROW SELECTED AND IS WHERE BELOW THIS ROW WILL BE
'INSERTED THE ROWS 55 TO 67 COPIED FROM THE SHEET "ORIGIN"
Dim a As Integer
20 Sheets("ORIGIN").Select
30 Rows("55:67").Select
40 Selection.Copy
50 Sheets("TARGET").Select
60 ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
70 Selection.Insert Shift:=xlDown
80 Application.CutCopyMode = False
90 On Error GoTo 0
100 Exit Sub
InsertCopiedCells_Error:
110 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure InsertCopiedCells, line " & Erl & "."
End Sub

 

[ravisingh]

Hi
I have an error -2147417848 of Automation on line 70 when i use this macro, sometimes the first execution, and always the second:


Public Sub InsertCopiedCells()
10 On Error GoTo InsertCopiedCells_Error
'IT IS NECESSARY TO CALL THIS MACRO FROM THE SHEET "TARGET"
'WITH A ROW SELECTED AND IS WHERE BELOW THIS ROW WILL BE
'INSERTED THE ROWS 55 TO 67 COPIED FROM THE SHEET "ORIGIN"
Dim a As Integer
20 Sheets("ORIGIN").Select
30 Rows("55:67").Select
40 Selection.Copy
50 Sheets("TARGET").Select
60 ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
70 Selection.Insert Shift:=xlDown
80 Application.CutCopyMode = False
90 On Error GoTo 0
100 Exit Sub
InsertCopiedCells_Error:
110 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure InsertCopiedCells, line " & Erl & "."
End Sub

Try replacing the highlighted code with:

20 Sheets("ORIGIN").Rows("55:67").Copy
50 Sheets("TARGET").Select
60 ActiveCell.Offset(1, 0).Insert Shift:=xlDown

 

[aitorsol]

Thanks a lot
Now the automation error has completely disappeared.