Simple VBA problem (but not for me)

[billy idol]

Please could someone help with a simple macro.

I have a sheet (call it "Calcs") and in cells a1..c1 I have formulae that feed off a number of other formulae and random numbers elsewhere in the same sheet. Instead of pressing F9 repeatedly and copying and pasting (special)each simulation, there must be an easy way. Let's say I want to copy this to another sheet "Results".

So what I want is:

For a = 1 To 1000
Calculate
Copy a1..c1 in Calcs to cell a1..c1 in Results
Calculate
Copy a1..c1 in Calcs to cell a2..c2 in Results
etc...
Calculate
Copy a1..c1 in Calcs to cell a1000..c1000 in Results.
End

Thanks

[NoName]

Sub test()
    Dim src As Worksheet, dest As Worksheet
    Set src = Worksheets("calcs")
    Set dest = Worksheets("results")
    For a = 1 To 1000
        Application.Calculate
        dest.Cells(a, 1).value = src.Cells(1, 1).value
        dest.Cells(a, 2).value = src.Cells(1, 2).value
        dest.Cells(a, 3).value = src.Cells(1, 3).value
    Next
End Sub

You can replace the 3 "dest=src" lines with a copy, but I prefer to avoid copies if reasonable so I can copy and paste other things while the macro is running.

[billy idol]

Thank you.

[KRWarner]

Originally Posted by NoName

Sub test()
    Dim src As Worksheet, dest As Worksheet
    Set src = Worksheets("calcs")
    Set dest = Worksheets("results")
    For a = 1 To 1000
        Application.Calculate
        dest.Cells(a, 1).value = src.Cells(1, 1).value
        dest.Cells(a, 2).value = src.Cells(1, 2).value
        dest.Cells(a, 3).value = src.Cells(1, 3).value
    Next
End Sub

You can replace the 3 "dest=src" lines with a copy, but I prefer to avoid copies if reasonable so I can copy and paste other things while the macro is running.

I would also add after the dim line.

Application.Calculation = xlCalculationManual

This will make sure the calculation mode is turned off. I haven't tested the value commands shown, but if you copy paste each cell and don't have calculations turned off the B1 and C1 values will be form differnt Random Number generations.

[mark007]

To add to this you should probably store the initial calculation mode and reset it to ensure users aren't annoyed by the change in calc mode. It will also run faster if you do the following:

1. Store the values in an array and output all at once
2. Turn off screen updating.

Finally, I've added a progress message in the status bar.

Sub test()

    Dim src As Worksheet, dest As Worksheet
    Dim OldCalc As XlCalculation
    Dim Iterations As Long
    Dim i As Long
    Dim Results() As Double
    
    OldCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = True
    
    'set iterations
    Iterations = 1000
    ReDim Results(1 To Iterations, 1 To 3)
    
    Set src = Worksheets("calcs")
    Set dest = Worksheets("results")
    
    For i = 1 To Iterations
        Application.Calculate
        Results(i, 1) = src.Cells(1, 1).Value
        Results(i, 2) = src.Cells(1, 2).Value
        Results(i, 3) = src.Cells(1, 3).Value
        Application.StatusBar = Format(i / Iterations, "0.00%") & " complete..."
    Next
    
    dest.Range("a1").Resize(Iterations, 3).Value = Results
    
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.Calculation = OldCalc
End Sub