VBA: Copying Formulas and Array Formulas at the same time

[Omom]

Hi,

I am struggling with the following code, that in essence is trying to copy a range of formulas from one sheet to another:

Sub CopySheetToSheet()
Dim r, c As Integer
Dim rngA As Variant

'HC Reference to Sheet5 Range Alpha
Set rngA = Sheet5.Range("Alpha")
r = rngA.Rows.Count
c = rngA.Columns.Count

Sheet7.Range("A1").Resize(r, c).FormulaArray = rngCF.FormulaArray

End Sub

When I amend the code that copies from rngA (Sheet 5) to Sheet7 by replacing the ".FormulaArray" Function with ".Formula" it works perfectly fine. Effectively it copies the entire range of Sheet 5 to Sheet 7. However, in rngA there are a number of rows with ArrayFunctions which I need to bring over to Sheet 7. As soon as I run the code as posted above (with the FormulaArray Function) a blank Sheet 7 is returned (i.e. the code seems to copy blanks from rngA to Sheet 7 and not the functions within rngA).

Further I need this to be dynamic - the code is supposed to copy rngA to a large number of Sheets (Sheet 7, 8, 9... and so on). So it needs to be fast (probably by-passing the clipboard ?). Last but not least - can rngA be defined as range as opposed to a variant ?

Any explanation what I am missing is greatly appreciated.
Thanks in advance.

 

[Omom]

Apologies - there was a mistake in my code (marked in red). See correction below:

Sub CopySheetToSheet()
Dim r, c As Integer
Dim rngA As Variant

'HC Reference to Sheet5 Range Alpha
Set rngA = Sheet5.Range("Alpha")
r = rngA.Rows.Count
c = rngA.Columns.Count

Sheet7.Range("A1").Resize(r, c).FormulaArray = [COLOR=#ff0000][B]rngA[/B][/COLOR].FormulaArray

End Sub

 

[victorvegas]

Anyone got a solution? Having a similar problem here.