Share via

Run-Time Error 1004: Insert Method of Range Class Failed, Excel 2010

Anonymous
2017-12-13T11:55:54+00:00

I currently use an Excel spreadsheet (Excel 2010, Windows 7) as a job list for a manufacturing company. When jobs are completed & shipped, a macro is used to archive the completed jobs from the main list/spreadsheet to a 'shipped' sheet in the same workbook. This workbook has been setup to function this way for several years, and has worked just fine until today. Now, when using my command to archive the completed jobs, I receive a pop-up box containing the following error message:

"Excel cannot complete this task with available resources. Choose less data or close other applications."

And when I click 'OK', I receive a box with this message:

"Run-time error '1004': Insert method of Range class failed"

And here is the code for the macro:

Sub ARCHIVE()

'

' ARCHIVE Macro

'

' Keyboard Shortcut: Ctrl+Shift+A

'

    Selection.Copy

    Sheets("Archive - Shipped").Select

    Rows("2:2").Select

    Selection.Insert Shift:=xlDown

    Range("A3").Select

    Sheets("SL Schedule").Select

    Application.CutCopyMode = False

    Selection.Delete Shift:=xlUp

    Range("A2").Select

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2017-12-13T14:54:45+00:00

So the selection isn't the issue. But - what cell is selected when you use Ctrl-End on sheet "Archive - Shipped"? You may have accidentally entered something in the bottom row, which would prevent inserting a row.

Go to what you think should be the last row, select a cell just below it and use Shift-End-DownArrow, then right click and choose Delete / Entire Row.  Then try your macro again.

Was this answer helpful?

4 people found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-12-13T17:31:35+00:00

    Nothing was entered below the bottom row on the 'Archive-Shipped' sheet, but it appears that this idea led to a solution. It seems that with 4+ years worth of jobs/rows the workbook wouldn't allow any additional lines to be added. Thanks for the help, Bernie!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-12-13T14:32:12+00:00

    I have an entire line selected on the main sheet (by clicking on the row # to the left of column 'A') when using the command CTRL+SHIFT+A, which is the same as I've always done. Once the line (or lines) have been archived and are on the "Archive-Shipped" sheet nothing further is done to/with the data other than using it as a running tab, so to speak, of what jobs have shipped.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2017-12-13T14:23:13+00:00

    What do you have selected when you run the macro?

    What cell is selected when you use Ctrl-End on sheet "Archive - Shipped"?

    Was this answer helpful?

    0 comments