 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / February 2006Calculate,Copy, Paste Cell Group
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 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 > 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 [quoted text clipped - 27 lines] > Application.Calculate = s > End Sub 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.  SignatureRegards, Tom Ogilvy > 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 [quoted text clipped - 27 lines] > Application.Calculate = s > End Sub 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 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 SignatureRegards, Tom Ogilvy > 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 works like a charm.....thanks 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 > I fixed your code to do that. I just added the rand() line for testing. > Take it out and your formula should work [quoted text clipped - 30 lines] > > and paste down 10,20, 50, 100 times depending on what ever amount i > > place in the macro..sorry for the misunderstanding 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.