Can anyone tell me why my cells reference doesn't work in 2010

[Darthzo]

I have had no issue with excel 2003 using the cell reference but in 2010 when i try to use this reference in the application it either skips over my commands with out entering a value or returns a runtime error(1004).

it only works with the sheet is identified ex...sheets("Sheet1") or activesheet.

Below is the simple macro I used to test this out

Please Login or Register  to view this content.

[Kaper]

first line writes to A1 in Sheet1
second to A2 in active sheet (it has to be a worksheet - cannot be for instance graph)
third selects A2 in active sheet (it has to be a worksheet)

if sheet1 is not an active sheet you will get both tests written into different sheets
If there is no Sheet1 in your workbook You will receive an error 9 - subscript out of range
As of error 1004 - isn't a sheet/workbook protected?

PS. Please use code tags as requested by forum rules (edit your post)

[Darthzo]

Test book.xlsx

Thank you Kaper

I have checked all three sheets in the workbook with only the code showing that has the "sheets1" reference actually posting data to the cells. See attachment

[Darthzo]

Attachment 307414

Thank you Kaper

I have checked all three sheets in the workbook with only the code showing that has the "sheets1" reference actually posting data to the cells. Also the workbook does not have any protection. See attachment

[JosephP]

where's the code located?

[Richard Buttrey]

Hi,

Seems to work OK for me. The workbook you posted is of course a .xlsx and by definition doesn't contain any macros so not quite sure why you attached it.

[Darthzo]

Sorry I actually had the code in my personal workbook. I was using that workbook at the time to test the macro. I am feeling this could be a settings issue as when i used my macros earlier in the week I had no issues.

[Richard Buttrey]

...maybe, although I suspect Excel 2007 onwards has perhaps been a bit more fussy about explicitly including sheet (and sometimes workbook) names when defining ranges.

[JosephP]

which module in the personal workbook was the code in? if it was in a worksheet module the second line would write to that sheet not the active sheet and the third line would error because you can't select the sheet in the personal workbook