Macro Runtime error 1004 "autofill" issue

Question

Macro Runtime error 1004 "autofill" issue

Jake Guy 0

So, i have a macro on a certificate generator, it adds rows to the "as found" table, as seen in the attached picture the "testpoint","set point" and "deviation" columns have formulas for the data. Below is the code used by the "ADD AS FOUND DATA ROW", but i get a runtime error on the autofill line for I38:I40, is there any advice or fix? the max that this table will be is 5 rows.

Sub Insert_DATA_Row()

' Insert_DATA_Row Macro

Rows("39:39").Select

Selection.Copy

Selection.Insert Shift:=xlDown

Range("A38:B38").Select

Application.CutCopyMode = False

Selection.AutoFill Destination:=Range("A38:B40"), Type:=xlFillDefault

Range("I38").Select

Application.CutCopyMode = False

Selection.AutoFill Destination:=Range("I38:I40"), Type:=xlFillDefault

Range("E39:J39").Select

Selection.ClearContents

Range("E39:G39").Select

End Sub

1 answer

Your answer

Answer 1

Rin-L 3,045 Microsoft External Staff Moderator

Hi @Jake Guy,

Thank you for posting your question in the Microsoft Q&A forum.

Based on my research, here are some common reasons why the Runtime Error 1004 might occur during the AutoFill operation in your macro:

Source cell (I38) lacks a formula: If I38 is blank or contains a static value, Excel doesn’t know what to autofill, which causes the error. → Make sure I38 contains a valid formula before running the macro.
Selection context issue: Using Range("I38").Select followed by Selection.AutoFill only works if I38 is actively selected and contains a formula. → Use a direct reference instead: Range("I38").AutoFill Destination:=Range("I38:I40")
Target range inconsistency: If row 40 doesn’t exist yet or the range isn’t properly formatted, Excel may throw an error. → Ensure rows 38 to 40 exist and are ready for autofill.

Here is a modified version of the macro with more secure autofill logic that you can try:

Sub Insert_DATA_Row()
 
    ' Insert a new row at 39
    Rows("39:39").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
    ' Autofill A and B columns
    Range("A38:B38").AutoFill Destination:=Range("A38:B40"), Type:=xlFillDefault
 
    ' Ensure I38 has a formula before autofill
    If Range("I38").HasFormula Then
        Range("I38").AutoFill Destination:=Range("I38:I40"), Type:=xlFillDefault
    Else
        MsgBox "Cell I38 must contain a formula before autofill."
    End If
 
    ' Clear contents in E to J for the new row
    Range("E39:J39").ClearContents
 
End Sub

Please let me know if you run into any issues or have updates. And in case I misunderstood your original question or intent, please feel free to clarify. We can work together to find a better solution if necessary. Your reply is valuable, and I’m here to support you through the process.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Jake Guy 0 Reputation points

2025-08-18T17:49:08.43+00:00

Hey! Thank you for the reply. this is the error the macro is giving me now, would you happen to know a fix? there is also splitting of the cells, as well as the "MPE" column not carrying over cell reference $H$16
Jake Guy 0 Reputation points

2025-08-18T17:49:38.8066667+00:00
Rin-L 3,045 Reputation points Microsoft External Staff Moderator

2025-08-20T07:33:49.9633333+00:00

Hi @Jake Guy,

Thank you for your reply!

I’m sorry to hear that the previous code didn’t work for your specific case. I did some research and found that another user encountered a similar issue about two months ago: How to fix a macro run time error 1004 - Microsoft Q&A

The error was reported because Visual Basic creates a reference to Excel when calling an object, method, or property without properly qualifying it with an Excel object variable. This reference isn’t released until the program ends, which can cause issues when the automation code runs multiple times. To resolve this, it’s recommended to make your code more specific by qualifying each call to an Excel object, method, or property before execution.

You can also refer to this helpful article: Excel VBA Runtime Error 1004 - How to Fix

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

If you're still encountering issues, feel free to share your Excel file with me via private message, along with a clear description of the result you're trying to achieve. I’ll do my best to investigate and assist you further.

To access private messages, click the banner as shown in the image below:

Please let me know if there are any updates. I'm looking forward to your response!
Rin-L 3,045 Reputation points Microsoft External Staff Moderator

2025-08-22T07:29:53.0366667+00:00

Hi @Jake Guy,

It has been a while and I am writing to see how things are going with this issue. Have you had a chance to check the replies provided? Any update would be appreciated.
Rin-L 3,045 Reputation points Microsoft External Staff Moderator

2025-08-28T08:24:44.8266667+00:00

Hi @Jake Guy,

I hope this message finds you well.

This is my second follow-up to check whether your issue has been resolved. I truly want to ensure your experience is as smooth as possible. If your problem is still ongoing or if you have faced any new difficulties, please share more details about the current steps you are stuck on. I am here and happy to provide further help whenever you need it.

While waiting for your response, I will continue tracking other customer questions on the forum to ensure everyone receives timely support. If you have additional questions, please feel free to tag my name in your reply so I can assist you promptly and more personally.

Looking forward to hearing from you soon.

Share via

Macro Runtime error 1004 "autofill" issue

1 answer

Your answer