Fix Excel VBA Runtime Error 1004: Range of Object Global Failed
Question details
The user needs to resolve VBA Runtime Error 1004 involving the Range method of the global object in an Excel macro.

- 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.
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.
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.
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.
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.
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)).
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.
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.

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.

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.




