excel - Issue when trying to set range values using ThisWorkbook when other sheet or workbook is selected -

why doesn't code work when executing macro if different sheet or workbook selected other sheet1? need code in format can change values in cell entries variables later on. there way work around this?

sub testhsa()     thisworkbook.sheets("sheet1").range(cells(1, 1), cells(2, 2)).value = 1 end sub 

it doesn't work because don't qualify cells component of statement.

you need following - notice . before cells:

with thisworkbook.sheets("sheet1")     .range(.cells(1,1), .cells(2, 2)).value = 1 end 

edit: further clarification comment. leaving . off worksheet/range/cell, telling excel want active parent. ie cells(1, 1) same activesheet.cells(1,1) , range("a1:d4") same activesheet.range("a1:d4").

the with statement tells excel follows associated 'with' object 3 lines of code same as:

thisworkbook.sheets("sheet1").range(thisworkbook.sheets("sheet1").cells(1,1), thisworkbook.sheets("sheet1").cells(2, 2)).value = 1 

this clarifies excel no matter activeworkbook want code access range in workbook code running from.

finally, if using ranges you'll want them assigned variable headofcatering has rightly suggested.