AutoFill method of range class failed '1004'

Am adding a line to an Excel table and I created a macro that fill down line from G to L to apply formula on new line.

Private Sub AutofillNewRows(ByRef modifiedRows As Collection)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Database")
    Set modifiedRows = New Collection
    
    Dim lastRow As Long
    Dim row As Long
    Dim col As Long
    Dim startColumn As Long
    Dim endColumn As Long
    Dim isRowEmpty As Boolean
    
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    
    

    startColumn = 7 ' Column G
    endColumn = 12 ' Column L
    
    ' Find last used row in column G
    lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).row

    ' Loop through each row from bottom to the first row of data
    For row = lastRow To 4 Step -1 ' Assuming row 4 is headers
        isRowEmpty = False
        For col = startColumn To endColumn ' Columns G to L
            If IsEmpty(ws.Cells(row, col)) Then
                isRowEmpty = True
                Exit For
            End If
        Next col
        
        If isRowEmpty Then
            ' Fill down the formulas for the range G:L from the row above
            
            Call AutoFillWithCopyMethod(ws, row)
            
            ' Add the row number to the collection
            modifiedRows.Add row
                        
            
            ' HN Handling
            ws.Cells(row, "HN").Formula = "=IF(ISNA(MATCH($A" & row & ",$A$3:$A" & row - 1 & ",0)),1,0)"
        End If
    
    Next row
End Sub

Sub AutoFillWithCopyMethod(ws As Worksheet, row As Long)
    ' Construct the range address as a string
    Dim sourceRangeAddress As String
    Dim destinationRangeAddress As String
    
    sourceRangeAddress = "G" & (row - 1) & ":L" & (row - 1)
    destinationRangeAddress = "G" & row & ":L" & row
    
    ' Perform the copy operation using the range addresses
    ws.Range(sourceRangeAddress).AutoFill Destination:=ws.Range(destinationRangeAddress), Type:=xlFillDefault

    
    ' If you want to only copy formulas without formatting or data validation
    ' ws.Range(destinationRangeAddress).Formula = ws.Range(sourceRangeAddress).Formula
End Sub

the problem is in this line : ws.Range(sourceRangeAddress).AutoFill Destination:=ws.Range(destinationRangeAddress), Type:=xlFillDefault

I tried passing the range as String because when I record the macro it does work and the parameters are passed as strings, but still getting error.

But when I change AutoFill to Copy it works.

>Solution :

Since you’re looping backwards because you have a guaranteed filled line at the end, you’d want your source range to be the below row, not the above one.

The issue for your error was not including the source range in the destination range, see this answer

Your second sub should look like this:

Sub AutoFillWithCopyMethod(ws As Worksheet, row As Long)
    ' Construct the range address as a string
    Dim sourceRangeAddress As String
    Dim destinationRangeAddress As String
    
    sourceRangeAddress = "G" & (row + 1) & ":L" & (row + 1)
    'the brackets aren't necessary as shown below:
    destinationRangeAddress = "G" & row & ":L" & row+1
    
    ' Perform the copy operation using the range addresses
    ws.Range(sourceRangeAddress).AutoFill Destination:=ws.Range(destinationRangeAddress), Type:=xlFillDefault

    
    ' If you want to only copy formulas without formatting or data validation
    ' ws.Range(destinationRangeAddress).Formula = ws.Range(sourceRangeAddress).Formula
End Sub

If you’re not going to use the collection values and just going to set it as a new collection, you’re better off declaring a new one or making it a Public one (between Option Explicit and the first sub) in case you will use the new collection in another sub.