'Array formulas and PasteSpecial:=Formats

I have this small issue in a workbook. I wanted to simply a very long function by using array formulas. The formula works well and produces the expected results. However, in my VBA macro I copy and then I do a formula paste and then a format paste. The format paste crashes with : "Impossible to paste because copy and paste zones are of different size" (or similar to that). This problem is solved if I use any other formula that is not an array formula.

Did anyone experience this before and find a proper solution ? I can't seem to find help an answer on Google. I'll post code if needed, but it is rather straight forward (Row.Copy ... Rows(something, something).PasteSpecial...) and works with a non-array formula anyway.

Edit: The code:

 sRapDetail = "rap - détails"

    Sheets(sRapDetail).Select
    iStart = 17
    iFormuleExceptionRNIAC = 13
    iFormule1 = 14
    iFormule2 = 15
    iFormuleExceptionAR = 16
    range(Rows(iStart - 1), Rows(1000)).Hidden = False

    iLastRow = Cells(65535, "B").End(xlUp).Row
    range("A" & iStart & ":AL" & iLastRow).Select
'    selection.Borders(xlInsideVertical).LineStyle = xlNone
'    selection.Borders(xlEdgeBottom).LineStyle = xlNone
    selection.Delete Shift:=xlUp

    'RNIAC
    Sheets("Zoom0").Select

    If Cells(21, "B").Value = "" Or Cells(22, "B").Value = "" Then
        iLastRow = 21
    Else
        iLastRow = Cells(21, "B").End(xlDown).Row
    End If
     iNbRow = iLastRow - 20

    Sheets(sRapDetail).Select
    Rows(iFormuleExceptionRNIAC).Select
    selection.Copy
    range(Rows(iStart), Rows(iStart - 1 + iNbRow)).Select
    selection.PasteSpecial xlPasteFormulas
    selection.PasteSpecial xlPasteFormats

(And yes I know the use of selection is ugly (or at least in this case) but this was written by a colleague)



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source