📎 This article includes 1 downloadable practice file ↓

Error 1004 (“Application-defined or object-defined error”) is Excel saying something about that range or object did not work. It is frustrating because the message rarely tells you what. These are the causes I run into most, roughly in order of how often.

1. Selecting a range on a sheet that is not active

Sheets("Data").Range("A1").Select   ' 1004 if "Data" is not the active sheet

Fix: do not select at all. Work with the range directly:

Sheets("Data").Range("A1").Value = "Hello"

The Macro Recorder tool shows how recorded code full of Select can be rewritten without it.

2. Mixing sheets in Range(Cells, Cells)

Sheets("Data").Range(Cells(1, 1), Cells(10, 3)).Copy   ' Cells() refer to the ACTIVE sheet

Fix: qualify every part:

With Sheets("Data")
    .Range(.Cells(1, 1), .Cells(10, 3)).Copy
End With

3. A row or column number of 0 (or too big)

Cells(0, 1) or Rows(lastRow + 1) when lastRow is already 1,048,576 both raise 1004. Usually a “last row” calculation returned something unexpected — see 5 ways to find the last row.

4. Invalid sheet or file names

Renaming a sheet to something longer than 31 characters, using / \ ? * [ ], or a name that already exists → 1004. Saving to a folder that does not exist does the same.

Dim nm As String
nm = Left(Replace(Replace(proposed, "/", "-"), ":", "-"), 31)

5. Protected sheet or workbook

Writing to locked cells on a protected sheet fails. Either unprotect in code, or protect with UserInterfaceOnly:=True so macros can still edit:

ws.Protect Password:="secret", UserInterfaceOnly:=True

6. Formula strings that Excel cannot parse

Range("B2").Formula = "=SUM(A1;A5)"   ' semicolon: 1004 in VBA

VBA’s .Formula always expects English function names and commas, whatever your regional settings. Use .FormulaLocal only if you really need local separators.

7. Copy/paste into a range of a different size or into merged cells

Pasting 10 rows into a merged area, or PasteSpecial when the clipboard is empty (because something in between cleared it), both raise 1004. Assign values directly instead of copy-pasting:

dest.Resize(src.Rows.Count, src.Columns.Count).Value = src.Value

How to find the real cause quickly

1. Click Debug — the yellow line is the culprit.
2. In the Immediate window (Ctrl+G) type ? ActiveSheet.Name and check the values of your row/column variables.
3. Ask: which sheet does this range belong to? Nine times out of ten that is the answer.

Try it yourself: step by step

1. Activate any sheet other than Data and run Sheets("Data").Range("A1").Select from the Immediate window — error 1004.
2. Run Sheets("Data").Range("A1").Value = "OK" instead — it works without selecting.
3. Try renaming a sheet to a 40-character name with ActiveSheet.Name = String(40, "x") — 1004 again.
4. Protect the sheet and try writing to it; then protect with UserInterfaceOnly:=True and retry.
5. Use the debugging checklist at the end of the article on your next real 1004.