VBA & Macro Problems

Fix Excel VBA Range and Cells References Across Worksheets

Emma BrownEmma Brown Nov 22, 2026 868 views

Question details

Correct an Excel VBA range expression that refers to cells on a different worksheet.

Fix Excel VBA Range and Cells References Across Worksheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA procedure

In Excel, open the Visual Basic editor from the Developer tab and find the procedure containing the Range and Cells expression.

2
Qualify the references

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.

3
Use the range directly

Run the procedure and use rng for the intended operation, such as rng.Value = 0, rather than relying on Select, Selection, or ActiveCell.

4
Activate the sheet if selection is required

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.

Qualify Range and Cells with the Target Worksheet
Check the workbook reference: ThisWorkbook refers to the workbook containing the VBA code. If the target sheet is in another workbook, replace ThisWorkbook with a reference to that workbook.
Free Microsoft Office alternative

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.

Open and edit Excel XLSX and XLS spreadsheetsWork with worksheet ranges using familiar spreadsheet controlsMove existing Office documents into a familiar interfaceUse a free, lightweight office suite for everyday tasks
microsoft office alternative - wps office

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.