Hi,
I am using the following macro to copy and insert rows in an Excel spreadsheet, based on a user inputted value. I place the cursor in the desired row, run the macro, which prompts for a number of rows. I enter a value and the macro works for awhile. However, after a few entries, I get an error message:
"Run-time error '-2147417848 (80010108)': Method 'Insert" of object 'Range' failed."
I am thinking that something is causing an "overload" which shuts down the macro. I would greatly appreciate help to resolve this problem. Thanks!
Greg
Sub Copy_And_Insert_Rows()
Dim x As Integer
x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
If x = False Then Exit Sub
ActiveCell.EntireRow.Copy
Range(ActiveCell, ActiveCell.Offset(x - 1, 0)).EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub
Hi, ac3mark,
Thanks for your reply and suggestion... Would you please clarify what you mean by testing x for 0 or 1? Do you mean revising the fourth line of the macro to read:
I also found that after entering a value (for number of rows to insert), that if I click OK in the dialogue box, all works well. If I use the enter key on my keyboard, an error message will sometimes appear. ?? No big deal, unless you see an immediate explanation. I will just be sure to use the OK button. Thanks!
Jan 13, 2016 at 08:04 PM
Thanks for your reply and suggestion... Would you please clarify what you mean by testing x for 0 or 1? Do you mean revising the fourth line of the macro to read:
If x = 0 then exit sub
Thanks!
Jan 14, 2016 at 04:41 PM
If you wish to test for true and false, set X as Boolean (DIM X AS BOOLEAN)
Jan 14, 2016 at 04:54 PM
Jan 14, 2016 at 04:56 PM
Jan 14, 2016 at 05:01 PM
I also found that after entering a value (for number of rows to insert), that if I click OK in the dialogue box, all works well. If I use the enter key on my keyboard, an error message will sometimes appear. ?? No big deal, unless you see an immediate explanation. I will just be sure to use the OK button. Thanks!