Calculate, Copy, Paste

[boblinda11]

Need help with a macro I can not for some reason get to work what I am
trying to do is copy a cell "AC12" and the paste it to "A1" and
then calculate "rand() " and copy "AC12" again and then paste
to column A2 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. Is it also possible to calculate,
copy and paste a number of cells say "AC12, AC21, AC28, AC33" etc.
and then paste all to column A this is what works now but I have to
calculate (PressF9) every time before I copy , paste Thanks in Advance

Range("AC12").Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone

End Sub

 

[Don Guillett]

I'm not sure about all that you want to do but this part is much better

Sub copyvalue()
x = Cells(Rows.Count, "a").End(xlUp).Row + 1
Cells(x, "a").Value = Range("ac12")
End Sub

to do it 5 times

Sub copyvaluemore()
For i = 1 To 5
Cells(i, "a").Value = Range("ac12")
Next i
End Sub

 

[boblinda11]

thanks don we are half way there..lol any way i need to calculate
"RAND()" every time before i copy and paste to "A1" and yes your macro
is MUCH better then mine if only i can get it to calculate RAND() which
means that every time i run RAND() i will get a new set of numbers to
copy and paste to "column A"

 

[Tom Ogilvy]

Sub copyvalue()
Dim n as Long, i as Long, x as Long
n = 12
for i = 1 to n
x = Cells(Rows.Count, "a").End(xlUp).Row + 1
Cells(x, "a").Value = Range("ac12")
Application.Calculate
Next
End Sub

For "AC12, AC21, AC28, AC33"
Sub copyvalue()
Dim rng as Range, i as Long, x as Long, j as Long
Dim cell as Range, n as Long
n = 12
set rng = Range("AC12, AC21, AC28, AC33")
for i = 1 to n
x = Cells(Rows.Count, "a").End(xlUp).Row + 1
j = 0
for each cell in rng
Cells(x + j, "a").Value = rng.Value
j = j + 1
Next
Application.Calculate
Next
End Sub

Would be a guess.

 

[boblinda11]

Sweet !!! thanks tom..having a problem with your second macro though
its posting 4 like numbers in column A every time it calutates..thanks
agin

 

[Tom Ogilvy]

It was a guess at what you wanted. If that isn't what you want, don't use
it.

 

[boblinda11]

Thanks again tom. Works great!!! Now if you could help me one more
time. Working on the same concept except I want to calculate Rand() and
then copy and paste 5 numbers example; A1, B1, C1, D1, E1 then paste to
AA1,BB1,CC1,DD1,EE1 then repeat the same procedure 25 times. Again let
me thank you as well as others who shear their knowledge with the rest
of us

 

[Tom Ogilvy]

Sub copyvalue()
Dim rng as Range, i as Long, j as Long, rng as Range
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("A1:E1")
v(1) = "AA" : v(2) = "BB" : v(3) = "CC"
v(4) = "DD" : v(5) = "EE"
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