Clumsy VBA Code for Chart Source Data from Loop
 

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

Clumsy VBA Code for Chart Source Data from Loop
 

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

Clumsy VBA Code for Chart Source Data from Loop
 

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.