Can anyone help me understand how to populate a temporary
array by row please. I am trying to take a column of
values H1:H256 on one sheet (Values change every time
sheet is calculated), and populate another sheet in the
range A2:IV1001. The current macro runs 1000 times and
works but is very slow, (even without selecting
anything). I thought that it might be quicker to
populate a temporary array row by row and then paste the
temporary array into the worksheet. I can understand the
logic in the following, but it does it cell by cell, and
what I'm struggling to understand is how I adapt it to
populate it row by row


Sub TranData2()

Dim i As Long
Dim TempArray() As Single
Dim TheRange As Range

ReDim TempArray(3 To 10, 4 To 16)
Set TheRange = Range(Cells(3, 4), Cells(10, 16))

CurrVal = 0
x = 1
For i = 3 To 10
For j = 4 To 16
TempArray(i, j) = Range("A3").Value
ActiveSheet.Calculate
Next j
Application.StatusBar = x
x = x + 1
Next i

TheRange.Value = TempArray

End Sub


I assume that I should use worksheetfunction.transpose to
transpose my column of values and then put them straight
into the temporary array, repeating and moving down a row
each time, but I can't work out how to do this.

The following (Ranges are test ranges only) is not
working but will give you some idea of what I'm trying to
achieve. Am I even on the right lines here?

Sub TranData()

Dim i As Long
Dim TempArray() As Long
Dim TheRange As Range

ReDim TempArray(3 To 10, 4 To 16)
Set TheRange = Range(Cells(3, 4), Cells(10, 16))

x = 1
For i = 3 To 10
TempArray(i) = WorksheetFunction.Transpose
("ColData").Value
ActiveSheet.Calculate
Application.StatusBar = x
x = x + 1
Next i

TheRange.Value = TempArray

End Sub

Thanks
Pete