The VBA Runtime Error 1004 can be caused by many things. In this article, I’ll show a few different situations and show how to deal with them.
VBA code refers to a range that doesn’t exist
This code usually happens when VBA code refers to a cell or range that doesn’t exist. For example, this code is correct.
Sub myFunc() Set Rng = Worksheets("Sheet1").Range("Z3") Set Rng2 = Worksheets("Sheet1").Range("ZZ3") End Sub
But when you try to access the following ranges, the program will return an error in both cases.
Sub myFunc() '>>>Error 1004 "Application-defined or Object-defined error" Set Rng = Worksheets("Sheet1").Range("ZZZ3") Set Rng2 = Worksheets("Sheet1").Range("myRange") End Sub
The first line returned 1004 error because Excel contains only 16,384 columns and the last column is XFD. In the second case, we don’t have the “myRange” range. In this case, the application also returns the error. After you create the range, the error won’t show up.
You should really pay attention when you are using loops, especially for columns. You can go out of the index without noticing.
You are trying to enter a value that is not a range
The following code will result in an error because the program cannot recognize values inside the Range property as a range.
Sub myFunc() '>>>Error 1004 "Application-defined or Object-defined error" Set Rng = Worksheets("Sheet1").Range(4) Set Rng2 = Worksheets("Sheet1").Range("4") End Sub
Select a worksheet explicitly
Sometimes when you choose a worksheet implicitly, you may have 1004 error.
Take a look at this code. Here, you selected the active sheet, but when you activate a different sheet this may result in an error.
Set wksSource = ActiveWorkbook.ActiveSheet
Instead, use the code when you explicitly select sheet name.
Set wksSource = ActiveWorkbook.Sheets("Sheet1")
You are using protected mode
You may also get the 1004 error if your worksheet is in the protected mode. Disabling the protection might get rid of this error.
Macro recorded at the worksheet level
Sometimes you may get this error if you record a macro at the worksheet level. You should create a module and enter the code there. In order to do so, go to VBE (Alt + F11) and right click on the module’s icon. Insert a new module and enter your code there.
These situations are the most common for error 1004 to occur. I hope it fixed your problem.