1

I keep getting the error, "Select Method of Range Class Failed" and unexpected results from Rows("2:2").Insert

Code below

Worksheets("Sheet3").Select

Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove    

Range("D4").Select

First the new row is added to Sheet 1 not sheet 3 and then Range cannot be selected 1004 error. I have used this exact script to select other worksheets and ranges without an issue. I can provide the full file and code for anyone who can help

Alex P
  • 12,249
  • 5
  • 51
  • 70
BKruk
  • 11
  • 1
  • 4
  • See [Worksheet .Name, .Index or .CodeName](http://stackoverflow.com/documentation/excel-vba/2240/excel-vba-tips-and-tricks/11272/worksheet-name-index-or-codename). –  Jul 28 '16 at 14:04

3 Answers3

0

Avoid the use of .Select. You may want to see This

Also ensure that your worksheet is unprotected.

Is this what you are trying? 

Worksheets("Sheet3").Rows(2).Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • I am using .select so that I have a permanent starting point. Later in the code I utilize a For Next loop For i = 1 To myarraycount ActiveCell.Value = BidItemsName(i) ActiveCell.Offset(0, 1).Value = BidItems(i) ActiveCell.Offset(0, 2).Value = BidItemQuant(i) ActiveCell.Offset(0, 3).Select Next i – BKruk Jul 29 '16 at 16:58
  • And that is exactly what you must ignore. Please re-read the link that I have given you :) – Siddharth Rout Jul 29 '16 at 17:27
0

The code below works, define the Sheet, and not using Select (the first time):

Dim sht3 As Worksheet

Set sht3 = ThisWorkbook.Sheets("Sheet3")

sht3.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
sht3.Range("D4").Select
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • When I use the code I provided in a different Sub() there is no issues, it only occurs in one of my segments. – BKruk Jul 29 '16 at 17:13
  • @BKruk then you need to provide the rest of your code, so we can see where and why are you getting this error – Shai Rado Jul 29 '16 at 17:18
0

When you are using select you'll always ensure that the particular sheet is active. If not activate it using activate property. Below is the code that does the work

Worksheets("Sheet3").Activate

Worksheets("Sheet3").Select

Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("D4").Select
Rajan
  • 95
  • 12
  • I was able to avoid the error by creating another Sub() and adding this code: 'code' Worksheets("Sheet3").Activate Worksheets("Sheet3").Select Rows("2:2").Select Selection.Insert Shift:=xlDown, copyOrigin:=xlFormatFromRightOrBelow Range("E2").Select – BKruk Jul 29 '16 at 17:06