Select method failed - Error 400

[Robert Crandal]

The following code generates an error in Line 5

Sheets("APR").Select
Rows("9:9").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Data").Select
Rows("25:25").Select ' Line 5 - Error happens here
Selection.Copy
Sheets("APR").Select
ActiveSheet.Paste

The error code is 400. The error description is "Select method
of Range class failed". I'm not sure why this error is occurring,
especially since the above code was generated by Excel's
"Record Macro" function.

Why would the above code work during "Record Macro",
but it generates an error when I run it from VBA code?
I basically have a button on my "APR" spreadsheet that calls
the above code.

Anybody know what's going on here?

 

[Claus Busch]

Hi Robert,

Am Sat, 6 Apr 2013 13:43:24 -0700 schrieb Robert Crandal:

Sheets("APR").Select
Rows("9:9").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Data").Select
Rows("25:25").Select ' Line 5 - Error happens here
Selection.Copy
Sheets("APR").Select
ActiveSheet.Paste

you only can select on a active sheet. But you can write your code
without "Select" and "Selection".
Try:

Sheets("APR").Rows("9:9").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Data").Rows("25:25").Copy _
Sheets("APR").Range("A9")


Regards
Claus Busch

 

[Robert Crandal]

you only can select on a active sheet. But you can write your code
without "Select" and "Selection".
Try:

Sheets("APR").Rows("9:9").Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Data").Rows("25:25").Copy _
Sheets("APR").Range("A9")

Works great. Thanks yet again! 8)