Fix Excel VBA Range and Cells References Across Worksheets
Question details
Correct an Excel VBA range expression that refers to cells on a different worksheet.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro needs to reference a range on a worksheet other than the sheet implied by unqualified references.
- Observed behavior
- The range expression may produce Runtime Error 1004 when Range and Cells refer to different worksheets.
Identify the workbook and worksheet that contain the cells you want to reference, and check that the worksheet name in the code matches its tab name. Open the VBA editor and locate the procedure that creates or selects the range.
Qualify Range and Cells with the Target Worksheet
Use the same worksheet for Range and both Cells references to prevent cross-sheet reference errors.
Unqualified Cells references can point to the worksheet containing the code or to the active sheet, depending on where the code runs. A With block makes the intended worksheet explicit.
In Excel, open the Visual Basic editor from the Developer tab and find the procedure containing the Range and Cells expression.
Replace the expression with: Dim rng As Range With ThisWorkbook.Worksheets("Sheet2") Set rng = .Range(.Cells(2, 9), .Cells(100, 9)) End With Change Sheet2 and the cell coordinates to match your target. The leading dots bind Range and both Cells calls to the same worksheet.
Run the procedure and use rng for the intended operation, such as rng.Value = 0, rather than relying on Select, Selection, or ActiveCell.
If your code must select the range, add ThisWorkbook.Activate, then rng.Worksheet.Activate, followed by rng.Select. A range can be selected only when its parent worksheet is active.

Work with Excel Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for everyday spreadsheet work, with a familiar interface and Excel format compatibility that helps you move existing files. It does not fix errors in Microsoft Excel, and VBA compatibility may vary.

Frequently Asked Questions
Why does a Range expression produce Runtime Error 1004?
One common cause is that Range and its Cells arguments refer to different worksheets. Use a With block for the target worksheet and prefix Range and both Cells calls with a dot.
What do the dots before Range and Cells mean?
Inside With ThisWorkbook.Worksheets("Sheet2"), the dots in .Range and .Cells refer to that worksheet. This keeps all parts of the expression on Sheet2.
Do I need to activate a worksheet to change a range?
No. You can work directly with a qualified range, such as rng.Value = 0, without activating its worksheet. Activation is needed if you want to select the range.
How do I select a range on another worksheet?
After setting rng to a qualified range, activate its workbook and worksheet with ThisWorkbook.Activate and rng.Worksheet.Activate, then use rng.Select. Use the target workbook reference instead of ThisWorkbook if the range is elsewhere.




