reference to sheet by name behaviour I cannot explain

[nabuzzard]

I have a VBA module that seeks to clear the contents of a range of cells on a sheet. The code line that does this is:

ThisWorkbook.Worksheets("core").Range(Cells(1, 10), Cells(2, last_col)).ClearContents

I am certain that the last_col variable is an integer, and has a valid value. Oddly, when this runs (the code executes automatically on opening the workbook) I get an error. If I then click to rename the worksheet, but make no change to the worksheet name (just right click, select rename then click off to exit the rename mode), and rerun the code it works fine, despite there being not change to the code or the sheetname!

I have looked all over and cannot understand what is changing by going into rename sheet? I assume that it is in some way forcing excel to recognise the sheetname where it had not previously - but why would this be?

I have not posted the full code at it is very extensive, with multiple modules, userforms and class modules. It is only this bit that gives the error, and then not always. I can apply a workaround, but wondered if anyone can explain the cause of the behaviour.

Thank you in advance to anyone who has insight
N

 

[mohadin]

Well .
Testing this line of code working ok
Must be something else where

 

[Norie]

You don't have workbook/worksheet references for Cells.

With ThisWorkbook.Worksheets("core")
    .Range(.Cells(1, 10), .Cells(2, last_col)).ClearContents
End With
[/vba]

 

[nabuzzard]

You don't have workbook/worksheet references for Cells.

With ThisWorkbook.Worksheets("core")
    .Range(.Cells(1, 10), .Cells(2, last_col)).ClearContents
End With
[/vba]

Spot on, I had assumed in error that when defining a range on a sheet that any cells identified would automatically be on that sheet. Teach me to assume anything in vba.

Thank you for assisting - the revised code works seamlessly.

N