Results 1 to 2 of 2

Thread: Simple Copy & Paste for ranges , but with random values

  1. #1

    Simple Copy & Paste for ranges , but with random values



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I cannt understand, why my excel script does not work anymore.. I need to do simple copy Paste function,
    First, I have 1 value (Mean) which is generated and it changes randomly after any action (Very important point!)
    So , I need a kind of "simulation", means to copy the value and put it in another worksheet, after that the excel is refreshing automatically and I get a new mean-value..
    This process should be repeated 1000 times. It means i will have 1000 different values at the end, because of random changes of my mean-value
    I have a script, which works perfect for this purpose.

    Dim i AsInteger
    Sheets("Worksheet2").Select
    Cells(4,23).Select
    Selection.Copy
    '
    Sheets("Worksheet3").Select
    For i =1To1000
    Cells(1+ i,1).Paste
    Special Paste
    :=xlPasteValues
    Next i
    '
    EndSubNow,

    I have not only one cell (my mean-value) but two! ( median from the same data)

    It means, now it should copy two cells simultaneously (range) and paste than in the new worksheet, in the new range. So, it is the same process, like in my script, but with two cells
    a kind of this..

    Dim i AsInteger
    Sheets("Worksheet3").Select
    Range("W4:X4").Select
    Selection.Copy
    '
    Sheets("Workshit4").Select
    For i =1 To 100 Range("A2:B2"&1+ i).PasteSpecial Paste:=xlPasteValues
    Application
    .CalculateFull
    Next i
    '
    EndSub

    I tried to do it with range() BUT!!! it doesnt refresh my mean and median values anymore ... or something else... so I get 1000 times the SAME!! value after the script running.
    I can not understand, what is his problem. Why in first case it works perfect, and if I do the same task with range, it seems to copy the same value about 1000 times, but not refreshing or changing it.
    pleas, I hope, anybody could help me
    thank you very much!
    Last edited by deyneitz; 2015-01-18 at 10:12 PM.

  2. #2
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    Code:
    Sub M_snb()
        Application.Calculation = -4105
    End Sub

Tags for this Thread

Posting Permissions

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