VBA Error 1004 – Application-Defined or Object-Defined Error
In this Article
This tutorial will explain the VBA Error 1004- Application-Defined or Object-Defined Error.
VBA run-time error 1004 is known as an Application-Defined or Object-Defined error which occurs while the code is running. Making coding errors (See our Error Handling Guide) is an unavoidable aspect learning VBA but knowing why an error occurs helps you to avoid making errors in future coding.
VBA Error 1004 – Object does not exist
If we are referring to an object in our code such as a Range Name that has not been defined, then this error can occur as the VBA code will be unable to find the name.
Sub CopyRange() Dim CopyFrom As Range Dim CopyTo As Range Set CopyFrom = Sheets(1).Range("CopyFrom") Set CopyTo = Sheets(1).Range("CopyTo") CopyFrom.Copy CopyTo.PasteSpecial xlPasteValues End Sub
The example above will copy the values from the named range “CopyFrom” to the named range “CopyTo” – on condition of course that these are existing named ranges! If they do not exist, then the Error 1004 will display.
The simplest way to avoid this error in the example above is to create the range names in the Excel workbook, or refer to the range in the traditional row and column format eg: Range(“A1:A10”).
VBA Error 1004 – Name Already Taken
The error can also occur if you are trying to rename an object to an object that already exists – for example if we are trying to rename Sheet1 but the name you are giving the sheet is already the name of another sheet.
Sub NameWorksheet() ActiveSheet.Name = "Sheet2" End Sub
If we already have a Sheet2, then the error will occur.
VBA Error 1004 – Incorrectly Referencing an Object
The error can also occur when you have incorrectly referenced an object in your code. For example:
Sub CopyRange() Dim CopyFrom As Range Dim CopyTo As Range Set CopyFrom = Range("A1:A10") Set CopyTo = Range("C1:C10") Range(CopyFrom).Copy Range(CopyTo).PasteSpecial xlPasteValues End Sub
This will once again give us the Error 10004
Correct the code, and the error will no longer be shown.
Sub CopyRange() Dim CopyFrom As Range Dim CopyTo As Range Set CopyFrom = Range("A1:A10") Set CopyTo = Range("C1:C10") CopyFrom.Copy CopyTo.PasteSpecial xlPasteValues End Sub
VBA Error 1004 – Object Not Found
This error can also occur when we are trying to open a workbook and the workbook is not found – the workbook in this instance being the object that is not found.
Sub OpenFile() Dim wb As Workbook Set wb = Workbooks.Open("C:\Data\TestFile.xlsx") End Sub
Although the message will be different in the error box, the error is still 1004.
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!