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

2
votes

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).

enter image description here

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).

2
excelvba
Have you removed (deleted) any worksheets? How many worksheets does the workbook contain? - user4039065
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
And worksheet(21) may not be the same thing as Sheet21. What does ?worksheets.count report in the VBE's Immediate window? - user4039065
once you click on the link in my post you can see the structure of the worksheets - AbePL
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

2 Answers

2
votes

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
2
votes

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: