VBA & Macro Problems

Fix Excel VBA Runtime Error 1004: Range of Object Global Failed

Bushra ParveenBushra Parveen Nov 18, 2026 869 views

Question details

The user needs to resolve VBA Runtime Error 1004 involving the Range method of the global object in an Excel macro.

Fix Excel VBA Runtime Error 1004: Range of Object Global Failed
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
Debugging an Excel macro that refers to a worksheet range.
Observed behavior
The macro stops with Runtime Error 1004: Method Range of Object Global Failed.
Before you start

Open the workbook containing the macro and check the exact name of the worksheet and range the code should use. If the data is in a different workbook, make sure that workbook is open.

Solution 1Recommended

Qualify Range references with the correct workbook and worksheet

Use this approach when the macro relies on an unqualified Range reference or the active sheet may not be the intended sheet.

1
Find the failing line

Open the VBA editor from Excel's Developer tab using Visual Basic, then run the macro and select Debug when the error appears. Examine the highlighted Range or Cells reference.

2
Set the worksheet

In the macro, declare and set a worksheet variable, for example: Dim ws As Worksheet, followed by Set ws = ThisWorkbook.Worksheets("Sheet1"). Replace Sheet1 with the exact worksheet tab name.

3
Qualify the range

Replace an unqualified reference such as Range("A1") with ws.Range("A1"). For ranges built from cells, qualify both endpoints too, for example: ws.Range(ws.Cells(1, 1), ws.Cells(10, 1)).

4
Check the workbook and address

Confirm that the address passed to ws.Range is valid and that the worksheet belongs to the intended workbook. ThisWorkbook refers to the workbook containing the code; if the data is elsewhere, use a reference to that open workbook instead.

5
Test the macro

Run the macro again from the VBA editor using Run > Run Sub/UserForm. If the error remains, test the relevant code in a small sample workbook with the same sheet name and range.

Qualify Range references with the correct workbook and worksheet
Check other range references: Review other unqualified Range and Cells references in the same procedure. Relying on ActiveSheet can cause code to target a different worksheet than intended.
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 support for common Excel formats to help you move existing files. Fix and test Excel-specific VBA errors in Microsoft Excel; macro support can vary in WPS Office.

Open and edit common Microsoft Excel formats, including XLSX and XLSReview worksheet names, cell addresses, and spreadsheet data in WPS SpreadsheetsUse a familiar spreadsheet interface for everyday tasksMove existing spreadsheets into a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does an unqualified Range reference cause Error 1004?

An unqualified Range reference depends on context, such as the active sheet. If that context is not the intended worksheet or the address is invalid, the macro can fail. Use a worksheet reference such as ws.Range("A1").

What does ThisWorkbook refer to in VBA?

ThisWorkbook refers to the workbook containing the VBA code, not necessarily the workbook currently active in Excel. If the data is in another open workbook, reference that workbook before selecting its worksheet.

Do I need to qualify Cells inside a Range reference?

Yes. Use the same worksheet for the range and its endpoints, such as ws.Range(ws.Cells(1, 1), ws.Cells(10, 1)), to avoid mixing worksheet contexts.

How can I find the line causing Runtime Error 1004?

When Excel displays the error, select Debug to highlight the failing line in the VBA editor. Check its workbook, worksheet, and range address, then run the macro again.