Using an Array to Speed-Up Range Copy

[JingleRock]

I have a large row of formulas (183 cells) in a spreadsheet that I
want to copy a variable number of times (could be as many as 2,000).

My current working code:

With Sheets("WORKPLACE")
.Range(.Cells(5, 4), .Cells(5, 4).Offset(0, 182)).Copy _
Destination:=.Range(.Cells(6, 4), .Cells(totalrows, 4))
End With

My thought is to create a variant array:

vtFormulas =
WorksheetFunction.Transpose(WorksheetFunction.Transpose(ActiveSheet.Range
_
(Cells(5, 4), Cells(5, 186))))

What is the best way to copy this array down from Cells(6, 4) to
Cells(totalrows, 4)?
I want cell references to change relative to the respective row.

Thanks for a response.

 

[Bill Renaud]

With Worksheets("WORKPLACE")
.Cells(5, 4).Resize(totalrows, 183).FillDown
'or
.Range("D5").Resize(totalrows, 183).FillDown
End With

 

[JingleRock]

Thanks very much, Bill.
I gave up on the variant array and used your code; it appears to be
extremely efficient.
JingleRock

 

[JingleRock]

I tried to apply your code to copying a single column, with variable
length, to a cell on another worksheet; it did not work. Is this
possible?

JingleRock

 

[Bill Renaud]

In general, to copy a whole sheet of data from one sheet to another, use
code like the following.
Change the names of the worksheets to whatever you need.

Public Sub Test()
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim rngSource As Range

Set wsSource = Worksheets("Source")
Set wsDest = Worksheets("Dest")
Set rngSource = wsSource.UsedRange

rngSource.Copy wsDest.Range("A1")
End Sub