I have written VBA code for a Loop that generates 50 different outcomes by
changing the input variable to a formula (TotalPL). The module generates a
50 row list of the iteration of the variable stepping it up by 1 for each
count. I then use the list to create a graph of the impact of each variable
on the outcome.
To do this I have written a copy and paste routine to capture the outcome
for each iteration of the loop. It looks very inefficient, and takes a long
time as the screen flashes from Worksheet1 to Worksheet2 and back for each
cut and paste iteration.
(the source data list ends up on Sheet1)
I would much appreciate any suggestions to accomplish this more elegantly.
As is evident I am an unskilled programmer!
My code (based on John Walkenbach's book) is as follows:

Sub GoodLoop3()
StartVal = 1
TotalPL = Application.Sheets("Sheet2").Range("B32")
NumToFill = Sheets("Sheet2").Range("E17")
' Following takes place on Sheet1
Range("B3").Value = StartVal
For Cnt = 0 To NumToFill - 1
Range("B3").Offset(Cnt, 0).Value = StartVal + Cnt
Range("B3").Offset(Cnt, 0).Copy
Sheets("Sheet2").Select
' Range E12 is the input variable whcih needs 50 different values input into
it
Range("E12").Select
Selection.PasteSpecial Paste:=xlValues
Range("B32").Select
' Range B32 is the formula result which I would like to call TotalPL
Selection.Copy
Sheets("Sheet1").Select
Range("B3").Offset(Cnt, 1).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("B3").Offset(Cnt, -1).Select
Next Cnt
End Sub

First to prevent the screen flashing and save time, after the first
line (sub...) type in Application.ScreenUpdating = False.

Thank you Skiloa - that helped.
Any other suggestions welcomed

"skiloa" wrote:

First to prevent the screen flashing and save time, after the first
line (sub...) type in Application.ScreenUpdating = False.