Using an Array to Speed-Up Range Copy
 

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.Tran spose(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.

Using an Array to Speed-Up Range Copy
 

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

Using an Array to Speed-Up Range Copy
 

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

Using an Array to Speed-Up Range Copy
 

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

Using an Array to Speed-Up Range Copy
 

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

--
Regards,
Bill Renaud