Select Method of Range Object Failed

0

I recieved some strange results from the follong code:

Worksheets("Sheet3").select
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin := xlFormateFromRightorAbove
Range("E2").select

When I ran my routine a new row 2 was inserted into sheet 1 not sheet 3 and I recieved a 1004 Error, Select Method of Range Object Failed.

To resolve this I passed all my required arguments to a new sub routine and the results were as they should be (New row 2 in sheet 3 and the propper cell was selected). But I would really like to know why the code failed in my original sub

EXCEL FILE ADDED 7/29/16 @ 11:57 EST

Answer
Discuss

Discussion

Ok, that macro is larger than I expected and I only found the example where you reference the other macro and not the one that caused the problem. However, I actually meant to state this in my answer, and I will update it, but i think the issue was that you put the code into Sheet1 instead of a module. Then, when you put it into a module, it worked fine. This is because the code for Sheet1 is meant to execute on Sheet 1 as far as I know. Code in the module is usually meant to work anywhere in the workbook.
don (rep: 1247) Jul 29, '16 at 1:07 pm
Add to Discussion

Answers

0
Selected Answer

I copied your code into a simple macro:

Sub test()

Worksheets("Sheet3").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormateFromRightorAbove
Range("E2").Select

End Sub

I didn't change anything and it worked perfectly for me and I also have Excel 2010.

Maybe you had something else in the macro that caused the issue.

You might just try the macro as I included it above and see if you still get a bad result.

Update

I think the issue was that you put the code into Sheet1 instead of a module. When you put it into a module, it worked fine. This is because the code for Sheet1 is meant to execute on Sheet 1 as far as I know. Code in the module is usually meant to work anywhere in the workbook. Your code is activating Sheet 3 and doing something there and since you run that macro from Sheet 1, it needs to be in a module given the way that you coded it.

That may sound confusing but I think that ws the problem in your case. Put macros into a Module by default.

Discuss

Discussion

Thank you Don, I did get the code to work by calling another Sub and formatting the worksheet there.
The syntax was correct orginally so it was more of a why this would happen question. VBA 1004 Error
I know there was nothing wrong with the code but was curious as to why or how the Select Method of a Range Object would fail in one Marco but work in every other instance. I can provide the workbook, unfortunatly you will see how limited my skills are. I am new to this site - is there a way to upload the workbook

 
BJKruk (rep: 2) Jul 29, '16 at 11:56 am
Edit your original question and upload it there. And the Select method fails basically when it can't select something in the way that you referenced it. 
don (rep: 1247) Jul 29, '16 at 1:02 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login