Hi,

Am Sat, 26 Apr 2014 20:04:55 +0100 schrieb Victor Delta:

I have a spreadsheet with a sequence of 6 random numbers in cells A1 to
A6. I would like to create a macro which copies the sequence down column
B (i.e. cells B1 to B6), then recalculates the random numbers and copies
the new sequence down the next 6 cells of column B, and so on until it
has filled x cells in this way - where x is determined by the number in
cell C1.

try:

Sub Randomize()
Dim arrOut As Variant
Dim LRow As Long
Dim myRows As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet
myRows = .Range("C1")
Do
arrOut = .Range("A1:A6")
LRow = .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Row
.Range("B" & IIf(LRow = 2, 1, LRow)).Resize(rowsize:=6) = arrOut
.Calculate
Loop While LRow + 6 < myRows
End With

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional