Calculate,Copy, Paste Cell Group

[boblinda11]

Need help with a macro I can not for some reason get to work what I am
trying to do is copy cells "AF78:AJ78" and then do a copy, paste
special to "AF81:AJ81" and
then calculate "rand() " and copy "AF78:AJ78" again and then paste
special
to column AF82:AJ82 and then repeat the same procedure 10, 50, 200
times if
wanted. I have no problem editing the macro for the number of times I
want to calculate, copy and paste.
Thanks in Advance

Sub afpaste()
Dim rng As Range, i As Long, j As
Dim cell As Range, n As Long, v(1 To 5) As String
Dim s As Long
s = Applicaton.Calculation
Application.Calculation = xlManual
n = 25
Set rng = Range("AF78:AJ78")
v(1) = "AF": v(2) = "AG": v(3) = "AH"
v(4) = "AI": v(5) = "AJ"
For i = 1 To n
Application.Calculate
j = 0
For Each cell In rng
j = j + 1
Cells(i, v(j)).Value = cell.Value
Next
Next
Application.Calculate = s
End Sub

 

[Guest]

Depending on what the formula is in AF78:AJ78 it looks like you could do it
all in one go:
With Range("AF81:AJ105")
.Formula = "=Rand() * 10"
.Formula = .Value
End With

Cheers,
Dave

 

[Tom Ogilvy]

Sub afpaste()
Dim rng As Range, i As Long, j As Long
Dim cell As Range, n As Long, v(1 To 5) As String
Dim s As Long
s = Application.Calculation
Application.Calculation = xlManual
n = 25
Set rng = Range("AF78:AJ78")
rng.Formula = "=rand()"
v(1) = "AF": v(2) = "AG": v(3) = "AH"
v(4) = "AI": v(5) = "AJ"
For i = 1 To n
Application.Calculate
j = 0
For Each cell In rng
j = j + 1
Cells(i + 80, v(j)).Value = cell.Value
Next
Next
Application.Calculation = s
End Sub


seemed to work for me.

 

[boblinda11]

sorry dave and tom the formula in AF78:AJ78 is a sum of a preexisting
numbers that change every time the sheet caclulates. all i was trying
to do is copy and paste the results of "AF78:AJ78" which has the
formula "SUM in AF78 to AJ78 then calulate sheet and repeat the copy
and paste down 10,20, 50, 100 times depending on what ever amount i
place in the macro..sorry for the misunderstanding

 

[Tom Ogilvy]

I fixed your code to do that. I just added the rand() line for testing.
Take it out and your formula should work

Sub afpaste()
Dim rng As Range, i As Long, j As Long
Dim cell As Range, n As Long, v(1 To 5) As String
Dim s As Long
s = Application.Calculation
Application.Calculation = xlManual
n = 25
Set rng = Range("AF78:AJ78")
v(1) = "AF": v(2) = "AG": v(3) = "AH"
v(4) = "AI": v(5) = "AJ"
For i = 1 To n
Application.Calculate
j = 0
For Each cell In rng
j = j + 1
Cells(i + 80, v(j)).Value = cell.Value
Next
Next
Application.Calculation = s
End Sub

 

[boblinda11]

works like a charm.....thanks

 

[boblinda11]

tom i also was hoping to make changes to the macro so i could use it to
copy and paste 6 numbers in another workbook and 4 numbers in still
another workbook and do the same thing but for some reason it will not
work. here are the changes i made to your macro thinking it would run
in the 6 number workbook

Dim cell As Range, n As Long, v(1 To ""6"") As String
v(4) = "AI": v(5) = "AJ" "": v(6) = "AK" ""
could you please tell me what i am missing to make it run....thanks

 

[boblinda11]

tom i also was hoping to make changes to the macro so i could use it to

copy and paste 6 numbers in another workbook and 4 numbers in still
another workbook and do the same thing but for some reason it will not
work. here are the changes i made to your macro thinking it would run
in the 6 number workbook

Dim cell As Range, n As Long, v(1 To ""6"") As String
v(4) = "AI": v(5) = "AJ" "": v(6) = "AK" ""
could you please tell me what i am missing to make it run....thanks