Runtime Error 1004: Why you can’t select a range on a Hidden Sheet.

VBA throws Runtime Error 1004: Select method of Range class failed whenever your macro calls the .Select or .Activate method on a cell or range that resides on a worksheet whose Visible property is set to xlSheetHidden or xlSheetVeryHidden.

Why Can’t You Select a Range on a Hidden Sheet in VBA?

You cannot select a range on a hidden sheet because Excel’s graphical user interface requires a worksheet to be actively displayed on screen before any cell inside it can receive user-interface focus. To modify hidden sheets, remove .Select and execute actions directly on the Range object.

The Immediate Fix: Eliminate .Select

The macro recorder relies heavily on .Select and Selection. In production VBA, .Select is almost never necessary and causes runtime failures whenever sheets are hidden or macros execute in the background.

' BROKEN: Fails with Error 1004 if "Calculations" is hidden
Worksheets("Calculations").Visible = xlSheetHidden
Worksheets("Calculations").Select
Range("A1").Select
Selection.Value = 500

' FIXED: Write directly to the Range without selecting it
Worksheets("Calculations").Range("A1").Value = 500

Direct manipulation executes faster, does not cause screen flicker, and runs reliably whether the target worksheet is visible, hidden, or very hidden.

---

Why the Error Happens

Excel maintains a strict operational boundary between the object model (the underlying data, properties, and values) and the user interface (what is physically rendered on your monitor).

When you call:

Worksheets("Report").Range("B2:D10").Select

You are issuing two instructions:

1. Identify the range object B2:D10 on Report.
2. Move the visual cursor in the Excel window to highlight those cells.

If Report is hidden (Visible = False), Excel cannot move the visual focus cursor to a tab that is not rendered on screen. The method fails and aborts execution with Runtime Error 1004.

---

How to Work with Hidden Worksheets Correctly

1. Direct Object Manipulation (Best Practice)

Virtually every operation you perform with .Select can be performed directly on the target object:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("AdminData")

' Copy data from hidden sheet to visible sheet
ws.Range("A1:C50").Copy Destination:=Worksheets("Summary").Range("A1")

' Clear formats or contents
ws.Range("D1:D100").ClearContents

' Set formulas
ws.Range("E2:E50").Formula = "=B2*C2"

2. When Selection Is Strictly Unavoidable

If you are automating an external add-in or legacy command that relies strictly on the active selection, you must temporarily unhide the sheet, perform the action, and re-hide it:

Dim ws As Worksheet
Dim originalVisibility As XlSheetVisibility

Set ws = ThisWorkbook.Worksheets("Backend")
originalVisibility = ws.Visible

' Unhide to allow UI selection
Application.ScreenUpdating = False
ws.Visible = xlSheetVisible
ws.Activate
ws.Range("A1:B10").Select

' Perform the legacy action here

' Restore original visibility
ws.Visible = originalVisibility
Application.ScreenUpdating = True

---

Escalation and Related Failures

If rewriting your code to avoid .Select still triggers a 1004 error on the same line, investigate these conditions:

• Sheet Protection: Writing directly to a locked cell on a hidden sheet triggers Runtime Error 1004: “The cell or chart you’re trying to change is on a protected sheet.”. Unprotect the sheet programmatically prior to writing.
• Malformed Range Calls: Confirm your direct reference is valid by reviewing Runtime Error 1004: Method ‘Range’ of object ‘_Worksheet’ failed..
• Background Execution: For architectural solutions to macros running in automated reports, see Runtime Error 1004: Why ActiveSheet references cause failures in background macros..

---