Error 1004 - 'select method of worksheet class failed' when worksheet is Selected

Question

Hi I have a problem with my code that is receiving the following error right at the last part of it:

Error 1004 - 'select method of worksheet class failed'

The code is basically a repetition of pretty much the same code that is doing stuff in 3 different tabs, the problem occurs at the very last tab (sheet21) and I can't understand why as it's working for 2 other tabs with no problem.

So the code for each tab looks the same with differences in excel formulas that are being populated in the table only.The problem occurs in sheet selection, sheet9, sheet11 and sheet21, the last one is where I receive an error, the previous tabs are being executed without any problem within the proper Tabs(sheets).

The whole code is very long and is adding columns with formatting and formulas, so here is just the part where I'm receiving an error:

 Sub AddColumns()
'Inserts Four Columns at L:O - Q3 Week High tab

Worksheets(21).Range("L:O").EntireColumn.Insert
'Format colour

Worksheets(21).Select        '-------1004 ERROR HERE 
Range("L4:N55").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

the worst part is that exactly the same construction of the code works for Worksheets(9) and Worksheets(11) that are being executed before in one go but as soon as I reach Worksheet(21) I'm receiving 1004 error and the process stops.

I've tried:

 If Worksheets(21).Visible = False Then Sheets(21).Visible = True
Sheets(21).Select

but then the code was being executed in some completely different tab Worksheet(19) for some reason and I don't understand why.

I'm using "Worksheet(#)" naming convention as the tabs are changing names on regular basis and using this format seemed to work fine before I've added Worksheet(21).

Have you removed (deleted) any worksheets? How many worksheets does the workbook contain? — , Oct 09 '18 at 10:45
`Sheets(21)` and `Worksheets(21)` won't always point at the same thing. The `Sheets` collection will include `Charts` as well as `Worksheets`. Are there `Charts` in your Workbook? — CLR, Oct 09 '18 at 10:48
And worksheet(21) may not be the same thing as Sheet21. What does `?worksheets.count` report in the VBE's Immediate window? — , Oct 09 '18 at 10:49
once you click on the link in my post you can see the structure of the worksheets — AbePL, Oct 09 '18 at 10:53
@AbePL - the structure is ok, no charts present, still it is a good practice, if you use `Worksheets()` instead of `Sheets()`. — Vityata, Oct 09 '18 at 10:54
There are only 17 sheets in your picture. 16 through 20 have been deleted. `Worksheets(21)` (the 21st worksheet in the workbook) will not be found. — CLR, Oct 09 '18 at 10:57

Vityata · Accepted Answer · 2018-10-09T11:48:54.057

Name, Index and CodeName are three properties of a worksheet, which are quite different. In the code from the screenshot, Sheet21 is a CodeName and it probably corresponds to Worksheets(17).

To see the difference, run the code below and take a look at the immediate window:

Sub TestMe()
    Dim i As Long
    For i = 1 To Worksheets.Count
        Debug.Print Worksheets(i).Name
        Debug.Print Worksheets(i).Index
        Debug.Print Worksheets(i).CodeName
        Debug.Print "-----------------------"
    Next i
End Sub

True and false would not help in If Worksheets(21).Visible = False, if the sheet is xlVeryHidden. xlVeryHidden is evaluated to 2 and this is evaluated to True.

Long story short:

Write Worksheets(21).Visible = xlVisible on the line before the select. Or Sheet21.Visible = xlVisible, as Worksheets(21) does not exist in your data.

And in general:

How to avoid using Select in Excel VBA

This is Brilliant!! thank you Finding out about the difference between index name and codename has helped , this sheet index is 12, I've refered to it by this index number and my code is working no problem !! you are Legend Vityata! — AbePL, Oct 09 '18 at 12:13

score 2 · Answer 2 · answered Oct 09 '18 at 11:02

You're attempting to select a sheet by it's index, and you're overreaching because you've deleted some of your sheets.

The tab called Sheet21 is likely referred to by index as Sheets(16) or Workheets(16). I can't be sure as I can't see the order of your tabs and the 16 here refers to the 16th tab as displayed on the tab bar (including hidden etc.) in order.

The following should work, regardless of the visibility of the tab when run.

Sub AddColumns()
  'Inserts Four Columns at L:O - Q3 Week High tab

  Sheet21.Range("L:O").EntireColumn.Insert
  'Format colour

  With Sheet21.Range("L4:N55").Interior
      .Pattern = xlNone
      .TintAndShade = 0
      .PatternTintAndShade = 0
  End With

If you want to force it to be visible, just use:

Sheet21.Visible = xlVisible

you are right, excel was overreaching, your suggestion is working as well as Vityata's solution, but I'd had to re-write a lot of this code so decided to go with his solution. All I had to do is to find out what is the index name of that worksheet, Vityata has helped me to find this out, I have adjusted my code to refer to correct index sheet name, and now all is working fine — AbePL, Oct 09 '18 at 12:20

Error 1004 - 'select method of worksheet class failed' when worksheet is Selected

2 Answers2