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 — user4039065
– user4039065, Commented Jan 17, 2018 at 6: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
– yashika vaish, Commented Jan 17, 2018 at 6: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
– yashika vaish, Commented Jan 17, 2018 at 6:58

DecimalTurn · Accepted Answer · 2020-04-14 07:48:21Z

2

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.

edited Apr 14, 2020 at 7:48

answered Jan 17, 2018 at 6:49

DecimalTurn

4,3833 gold badges20 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related