VBA - Select method failed

Question

I am a pretty new to VBA, so sorry if problem is obvious for more experienced users. I tried to read couple of answers to similar question, and fix the problem but still facing the same problem.

My code is:

Workbooks("XXX.xls").Activate

' Setting column width
Workbooks("XXX.xls").Worksheets("XXX").Cells.Select
Selection.ColumnWidth = 10

' Filtering XXX funds
Workbooks("XXX.xls").Worksheets("XXX").Rows("1:1").Select
Selection.AutoFilter Field:=3, Criteria1:="=*XXX*"

' Add new sheet and rename it
Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "XXX_F"

'Copying needed information
Workbooks("XXX.xls").Worksheets("XXX").Range("A1:C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

The error I am getting is "Run Time Error '1004': Select method of Range Class failed" on the third line from the bottom. I tried to fix it by adding this line (i.e. activating workbook I want to work with first):

Workbooks("XXX.xls").Activate

Also I used explicit references in my code, like:

Workbooks("XXX.xls").Worksheets("XXX").Range("A1:C1").Select

I understand that it is better not to use .Select method in my code at all. But it is not the first time I am getting this error, and I just want to understand VBA's logic. As I understand Excel just do not understand to which sheet I am referring, but I do not understand why - as I have activated the workbook I need (I have a couple of workbooks opened) and using explicit references. If you could please explain in detail why this error occurs (i.e in which point in time Excel confuses), I will be very grateful!

Thanks to everyone for help in advance!

just a hint - Excel has something called instances (if you are using Excel 2007 onwards). I suppose that in your case you have opened two incstances of excel. To fix it, close all excel files and then open the file with the code and the one with XXX just by clicking the mouse button. — Vityata, Sep 07 '16 at 12:50
This error is likely happening because, while you are activating the workbook, you also need to activate your worksheet before you can use the .select method on that sheet. So: `worksheets("XXX").activate` I know you said this already, but remember there are almost always better ways than to use a select statement. — Jason, Sep 07 '16 at 12:59
You can't select something on anything other than the active sheet. Best practice would be to refactor the code so that it doesn't ***use*** the `Active*` or global objects. Get references to your objects and use those instead. — Comintern, Sep 07 '16 at 13:03
Guys, just to understand you right - you are saying that .Select method can only be used to objects being previously activated? So, in my case the problem is after adding new sheet "XXX_F" (which by delault activates it) I tried to select Range on "XXX" sheet (by explicitly referring to it)? So, if I omit explicit reference my code will just select Range("A1:C1") on "XXX_F" sheet? And if I left explicit reference, but instead of .Select method will use .Copy - code will be working as it supposed to? — Oleksandr Titorchuk, Sep 07 '16 at 13:32
In a word, yes. `.Copy` doesn't require a range to be selected. — Comintern, Sep 07 '16 at 13:35

score 1 · Accepted Answer · edited May 23 '17 at 10:33

The comments on the question have sufficiently described the nature of the 1004 error, and you seem to have a good enough understanding of that.

See this great discussion for more detail:

How to avoid using Select in Excel VBA macros

Here is your code refactored to avoid Select method.

Dim wb as Workbook
Dim ws as Worksheet
Dim newWS as Worksheet
Dim rngCopy as Range

Set wb = Workbooks("XXX.xls")
Set ws = wb.Worksheets("XXX")

With ws
    ' Setting column width
    .Cells.ColumnWidth = 10

    ' Filtering XXX funds
    .Rows(1).AutoFilter Field:=3, Criteria1:="=*XXX*"

    Set rngCopy = .Range(.Range("A1:C1"), .Range("A1:C1").End(xlDown))
End With

' Add new sheet and rename it
With wb
    Set newWS = .Sheets.Add(After:=.Worksheets(.Worksheets.Count))
End With
newWS.Name = "XXX_F"

' Paste data in to the new worksheet
rngCopy Destination:=newWS.Cells(1,1)

Thanks a lot for help! – Oleksandr Titorchuk Sep 07 '16 at 14:37 — Oleksandr Titorchuk, Sep 07 '16 at 14:37

VBA - Select method failed

1 Answers1