reference to sheet by name behaviour I cannot explain

nabuzzard

New Member
Joined
Feb 8, 2008
Messages
6
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
792
Office Version
  1. 2013
Platform
  1. Windows
Well .
Testing this line of code working ok
Must be something else where
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
You don't have workbook/worksheet references for Cells.
VBA Code:
With ThisWorkbook.Worksheets("core")
    .Range(.Cells(1, 10), .Cells(2, last_col)).ClearContents
End With
[/vba]
 
Solution

nabuzzard

New Member
Joined
Feb 8, 2008
Messages
6
You don't have workbook/worksheet references for Cells.
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,750
Messages
5,574,016
Members
412,562
Latest member
woodportaj
Top