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

Question

Why doesn't the code work when executing the macro if a different sheet or workbook is selected other than sheet1? I need the code in this format so I can change the values in the cell entries to variables later on. Is there any way to work around this?

Sub testhsa()
    ThisWorkbook.Sheets("Sheet1").Range(Cells(1, 1), Cells(2, 2)).Value = 1
End Sub

Answer 1

It doesn't work because you don't qualify the Cells component of the statement.

You need the following - notice the . before the Cells:

With ThisWorkbook.Sheets("Sheet1")
    .Range(.Cells(1,1), .Cells(2, 2)).Value = 1
End With

Edit: Further clarification for the comment. By leaving the . off a worksheet/range/cell, you are telling Excel you want the Active parent. Ie Cells(1, 1) is the same ActiveSheet.Cells(1,1) and Range("A1:D4") is the same as ActiveSheet.Range("A1:D4").

The With statement effectively tells Excel that anything that follows is associated 'with' that object so my 3 lines of code is exactly the same as:

ThisWorkbook.Sheets("Sheet1").Range(ThisWorkbook.Sheets("Sheet1").Cells(1,1), ThisWorkbook.Sheets("Sheet1").Cells(2, 2)).Value = 1

This clarifies to Excel that no matter what is the ActiveWorkbook you want the code access the range in the workbook the code is running from.

Finally, if you are using ranges often you'll want them assigned to a variable as HeadofCatering has rightly suggested.