RunTime Error 2147417848 -Method insert of object Range Failed

[Vassen]

Hi,
When running below Code in my excel sheet , Error message >> Run time error 214741848 (80010108) >> Method "Insert" of object "Range" failed.
Please help

Please Login or Register  to view this content.

[TMS]

Works for me

Please Login or Register  to view this content.

As does:

Please Login or Register  to view this content.

[TMS]

Is the sheet protected? You'd get an Insert error for that scenario, though not necessarily the one you quote.

[Vassen]

Hi TMS,
No i do not have any lock cell, nor sheet protection. I have tried your new code but still same error message :

Please Login or Register  to view this content.

[TMS]

Suggest you post a sample of the workbook (that demonstrates the problem), if you can.

The code works in a newly created simple workbook, so it's not the code per se.

[Vassen]

Hi TMS,
Thanks for your quick response, i have uploaded a sample of the file(I had to cut the file as it is 39 MB.
Please note that with this file i had one trial it works and fails again at second attempt. Excel file is blocked. Had to close and reopen file , tried again same error message

[TMS]

Works for me. Executed code several times. The only issue that I had is that there are links in the file, presumably to the original. So, when I copy the rows, I get a File Open dialogue box ... I just press Escape to continue.

For example: [Supply Chain Version 29 Dec 2015.xlsm]NLM_LT'!$N$114.. Clearly, I don't have the relevant workbook OR worksheet.

[Vassen]

Hi TMS,
Thanks for your prompt reply and sorry to attend your thread only today as I was ill whole last week. Still problem when working on original file. Same error appears
The workbook i am working with consist of 20 worksheets which are all linked. Can the links between these sheets be a cause, can the size of the sheet (10 KB) be a problem. As there are formulas in the cells to be copied and insert, can the VBA actions be separated into 3 as follows:
1 >> Copy and insert Column A to S (as all are values)
2 >> Copy and paste special column T to XX in the inserted column in action 1 in T to XX
3 >> Execute formulas in inserted cell after action 2
Again apologies for late reply. Thanks for your help
Vassen

[Vassen]

Hi TMS,
You were right, it works withe the workbook containing only this worksheet, as I test same with the workbook with 20 worksheets linked, it gives ERROR message, How can this problem be overcome. Thanks for your help
Vassen

[JBeaucaire]

Maybe add this near the top of the macro:

Please Login or Register  to view this content.

Worth a try...

[Vassen]

Hi JBeaucaire,
Can you please precise what you mean by "Near the Top". Is it just before "ActiveCell.EntireRow.Copy. Note that it is not only giving the ERROR message but is also blocking the workbook, I have to close, do not save and re open to be able to work again
Thanks for your help
Vassen

[Vassen]

Hi JBeaucaire,TMS
I have tried but still same error message and it blocks the sheet,
Please help
Vassen

[xlnitwit]

Hi,

Does help at all?

Please Login or Register  to view this content.

[TMS]

Not sure what else to suggest. The code works, at least, it works in a new workbook/worksheet. So, the implication is that it is something to do with the data in the live file rather than a problem with the code.

As you cannot insert a row, I'm guessing that Excel sees data on the last row of the sheet. Maybe you have inadvertently copied a formula down to the last row. Or you have formatting or Conditional Formatting or Data Validation.

If you press Ctrl-End, which cell/row/column is selected? If it is row 1048576 that's probably the problem. In which case, you need to delete all the rows between the end of the real data and the last row. Select all the rows and use the Delete icon on the Ribbon, not the Delete key. Then save the file.

[Vassen]

Hi TMS,xlnitwit,
Thanks for your help.
Vassen

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.