Vba code getting error "select method of range class failed"

Question

In the code below, I am getting an error "Select Method of Range Class Failed" on the line bracketed by comments.

I am trying to copy some fixed ranges from Worksheet A of Workbook XY into Worksheet A of all the open workbooks. Kindly help me with the issues. Also, suggest a better or optimized way of doing the same.

Sub Macro1()
    Dim wbs As Workbooks
    Dim wb As Workbook
    Dim ws11111 As Worksheet
    Set wbs = Application.Workbooks
    For Each wb In wbs
    Set ws11111 = wb.Worksheets("A")

  '' ERROR BREAKS ON NEXT LINE:
    wb.Worksheets("A").Rows("1:1").Select
  '' ERROR: "Select Method of Range Class Failed"

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next wb
    Windows("XY.xlsx").Activate
    Worksheets("A").Select
    Workbooks("XY.xlsx").Worksheets("A").Range("E2:M6").Select
    Selection.Copy
    For Each wb In wbs
        wb.Worksheets("A").Range("E2:M6").Select
        ActiveSheet.Paste
    Next wb
    Windows("XY.xlsx").Activate
    Worksheets("A").Select
    Workbooks("XY.xlsx").Worksheets("A").Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Workbooks("XY.xlsx").Worksheets("A").Rows("9:9").Select
    Selection.Copy
    For Each wb In wbs
        wb.Worksheets("A").Rows("8:8").Select
        ActiveSheet.Paste
    Next wb
End Sub

1. You cannot select a range until you select the parent worksheet. 2. [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/28700020#28700020) — , Jan 17 '18 at 06:40
XY workbook is already open and I have tried wb.Worksheets("A").Rows("1:1").Select also, It is also giving me the same error. — yashika vaish, Jan 17 '18 at 06:57
Every time I am trying to select worksheet using wb, It is giving an error but without selecting worksheet, neither I can select rows or any range. — yashika vaish, Jan 17 '18 at 06:58

DecimalTurn · Accepted Answer · 2020-04-14T07:48:21.877

When you want to use the .Select method on a range, you have to make sure that both the corresponding workbook and worksheet are activated (in that order).

wb.Activate
ws11111.Activate
ws11111.Rows("1:1").Select

As you can see, the use of the .Select method can be a little tedious and it is also slow. This is why I would suggest you to have a look at this question, when you have the chance: How to avoid using Select in Excel VBA.

Vba code getting error "select method of range class failed"

1 Answers1