Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

excelmacros-vba
MS Office Version: 2010
posted by: BJKruk (rep: 2) on Fri Jul 29, 2016 at 10:07 am
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: 1989) 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.

posted by: don (rep: 1989) on Fri Jul 29, 2016 at 10:31 am
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: 1989) 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