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 ```

Accepted Answer

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.

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

Solution

Problem