Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    quick copy and paste (Office 2003)

    hello everyone;
    I need a bit of help.
    I am attaching a spreadsheet which has a statistical simulation:
    Values in column Y are result of values in columns A0, A1 and A2.
    A0, A1 and A2 are random variables with average and standard deviation shown in range J2 :L3.
    Their respective minimum (LL) and maximum (UL) possible values are shown in cells J4:L5 , assuming -/+ three standard deviations cover most of the population.
    Cells in columns F, G and H are random values generated by function RAND(). Every time you press function key F9, you will see how they change.
    Values in range J6:L7 are results of a linear correlation that allocates a new value to A0, A1 and A2 for each new value of the random value created at every F9 strike.
    So far for the description of the scenario:
    Now, my problem is that we need to plot 50 different “runs” (defining as a run every set of Y results when F9 is keyed).
    Columns U and V show already copied Y results and VALUES for two runs.
    Of course, I would not be asking for help if the scenario was like shown in this example (only 38 rows); because I would be doing it by hand myself.
    In the real situation, the workbook has 500 rows, and trying to copy and paste by hand would be tedious, arduous and prone to errors.
    Any suggestions on how to create a logical function or some other application of know function which would copy
    results in the remaining columns W to BR?
    Thanks lot in advance.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: quick copy and paste (Office 2003)

    Here's a macro to perform one run and copy the results:

    Sub RunAndCopyOnce()
    Dim m As Long
    Dim n As Long
    ' Next available target column
    m = Cells(2, Columns.Count).End(xlToLeft).Column + 1
    ' Last used data row
    n = Cells(Rows.Count, 5).End(xlUp).Row
    ActiveSheet.Calculate
    Range(Cells(2, 5), Cells(n, 5)).Copy
    Cells(2, m).PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    End Sub

    If you want to perform all runs at once:

    Sub RunAndCopyMany()
    Dim m As Long
    Dim n As Long
    ' Next available target column
    m = Cells(2, Columns.Count).End(xlToLeft).Column + 1
    ' Last used data row
    n = Cells(Rows.Count, 5).End(xlUp).Row
    ' Repeat runs
    Do While m <= 70
    ActiveSheet.Calculate
    Range(Cells(2, 5), Cells(n, 5)).Copy
    Cells(2, m).PasteSpecial Paste:=xlValues
    m = m + 1
    Loop
    Application.CutCopyMode = False
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Re: quick copy and paste (Office 2003)

    Thanks, Hans.
    I will try it this evening and let you know.
    Best regards;

  4. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Re: quick copy and paste (Office 2003)

    Hi Hans;

    YESSSSS!.
    I tried it and it works beautifully.
    Once again,

    Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •