'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 |
|---|
