Run Time Error '1004': Select method of Range Class failed using ThisWorkbook

Question

During the process of running a script if I manually remove focus from the Workbook containing the macro I get the error quoted. If I don't click on anything it works without issue. Script errors out only when I'm trying to place selection back into A1 from the "Input" sheet. Break point is on following line:

ThisWorkbook.Sheets("Input").Range("A1").Select

If I debug and place focus back on macro Worksheet the script completes without issue. Previous line:

ThisWorkbook.Sheets("Input").Cells.Delete

runs without error so I'm guessing its the range that is falling out of scope but don't quite understand why as it should be defined by the previous scope notations. Can someone explain why that line is falling out of scope? Shouldn't the ThisWorkbook define fairly explicitly the Workbook that my code is referencing? Any guidance is greatly appreciated.

Interesting. Would you do me a favor and test one thing? Replace `ThisWorkbook.Sheets("Input").Range("A1").Select` with `shtInputSheetCodeName.Range("A1").Select` and try it again. Of course you'll have to exchange the CodeName for the real one `? ThisWorkbook.Sheets("Input").CodeName` Another thing would be to `Activate` the sheet before `.Select`ing anything on the sheet. So, try to place a `ThisWorkbook.Sheets("Input").Activate` just before you `Select`. — Ralph, Mar 22 '16 at 22:03
Also see [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Siddharth Rout, Mar 23 '16 at 07:12
Same issue using CodeName. Note that it had the generic `Sheet1` CodeName which is identical to the only sheet from another open workbook. Any further ideas would be welcome. — nbayly, Mar 23 '16 at 15:16
@SiddharthRout I understand those concerns and I am not doing any data manipulation through the select. It is just to reset the sheet to it's initial stage with the top right hand cell selected. — nbayly, Mar 23 '16 at 15:22

Comintern · Accepted Answer · 2016-03-22T22:52:56.117

13

It doesn't have anything to do with the reference to ThisWorkbook at all. You simply can't Select a Range in an object that isn't active. Consider this code, which exhibits the same error:

Private Sub OneOhOhFour()

    'Executing with Book1.xlsm active and Book2.xlsx open.
    Dim wb As Workbook
    Set wb = Application.Workbooks("Book2.xlsx")
    Debug.Print ThisWorkbook.Name
    'Outputs 'Book1.xlsm' to Immediate window.
    wb.Sheets("Sheet1").Range("A1").Select   'Error 1004

End Sub

Same thing with Worksheets:

Private Sub OneOhOhFourVTwo()
    'Starting on anywhere but Sheet2 gives an error.
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    ws.Range("A1").Select  'Error 1004.
End Sub

The simple solution is to Activate the object before you Select within it:

Private Sub NoOneOhOhFour()

    Dim wb As Workbook
    Set wb = Application.Workbooks("Book2.xlsx")
    wb.Activate
    wb.Sheets("Sheet1").Range("A1").Select  'No error.

End Sub

Even better is using references and trying to avoid using the Selection and Active* objects entirely.

edited Mar 22 '16 at 22:52

answered Mar 22 '16 at 22:47

Comintern

21,855
5
33
80

Excellent explanation and resolution to my issue. As I mentioned on my comment above I am not doing any data manipulation with the `.Select` but just resetting the Worksheet to its initial state with focus set to the top right cell. Regards and thank you. – nbayly Mar 23 '16 at 15:33
1

I would recommend adding one more line to ensure that you do not get an error if the worksheet is hidden. `wb.Visible = xlSheetVisible` before the line `wb.Sheets("Sheet1").Range("A1").Select` – Siddharth Rout Mar 23 '16 at 15:42
@SiddharthRout love how your thinking of all possible situations. Since the users have to activate the sheet to trigger the script though I don't think this will be a situation that we will run into operationally. Thanks though for the idea :) – nbayly Mar 23 '16 at 16:26
Hi, I have a similar problem. Though i did the wb.activate and wb.Sheets("Sheet1").Range("A1").Select throwing the error selected method or range class failed. I am using office 2016 – user1844634 Sep 07 '17 at 07:36

score 0 · Answer 2 · edited Mar 13 '23 at 18:26

Sub hello()
    Dim mywkb As Workbook
    Dim file_path As String
    Dim file_name As String
    Let file_path = "C:\Users\LILY\OneDrive\Desktop\hello1\"
    Set mywkb = ThisWorkbook
    'looping
    For i = 2 To n
    Let file_name = Cells(i, 3) & " " & Cells(i, 4) & ".xlsx"
    Workbooks.Add.SaveAs file_path & file_name
    mywkb.Activate     ***'most important'***
    mywkb.Worksheets("employees").Range("a2").Select
    next
End sub

Need to activate workbook , before you select within workbook

Run Time Error '1004': Select method of Range Class failed using ThisWorkbook

2 Answers2

Linked

Related