Macro speed - copy & paste calculation loop

[chappy]

I have created a macro that cycles through a list of input variables from 2 separate tables. It copies input variables into 2 specific cells (C19 & C22). This action calculates a new value in cell C14 and that value is then copied and pasted into a table (E58:O69). Then it loops to the next 2 input variables and continues until it has cycled through and populated the entire table.

The process is extremely simple and it works but it is time consuming. I have several similar macros with the same file and it takes 30 minutes to cycle through all of the calculations. If it is possible to make improvements in the speed it would be extremely helpful.

I tried to search through the questions and content to see if I could find a similar issue. There may be examples in on the board but unfortunately I could not find one that helped me, if any suggestion could be made it would be much appreciated.

Sub Run_price_capex_sensitivities()
Application.ScreenUpdating = False

'Definie percentages for sensitivities
Set sensPriceRange = Range("D30:D40")
Set sensCapexRange = Range("E22:O22")

'Defintion data paste ranges
Set sensNPVCapexPasteRange = Range("E58:O58")

'Definition cells linked to QVM quick sensitivities
Set Primarysenscell = Range("C19")
Set SecondsensCapexcell = Range("C22")

'define number of rows for price
For n = 1 To 11

'copies primary sensitivity range
sensPriceRange(n, 1).Cells.Copy

'pastes primary sensitivity value to be used in calculation
Primarysenscell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'define number of columns for primary sensitivity
For i = 1 To 11

'copies capex sensitivity of x%
sensCapexRange.Cells(1, i).Copy

'pastes capex sensitivity 
SecondsensCapexcell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'gets NPV from cell C14
Range("C14").Cells.Copy

'Pastes NPV values for Capex sensitivity in row n and columns i
sensNPVCapexPasteRange.Cells(n, i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Next i
Next n

Application.ScreenUpdating = True

End Sub

 

[diddi]

First thing to do is add these 2 lines:

Application.ScreenUpdating = False
Application.Calculation = xlManual
. . .
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

 

[chappy]

First thing to do is add these 2 lines:

Application.ScreenUpdating = False
Application.Calculation = xlManual
. . .
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

Thanks for your answer diddi

It certainly speeded it up significantly. But unfortunately the removal of a calculation refresh generates a table of identical values.

The issue is that the calculations are made on a separate sheet which is linked to cells C19 and C22. The sequence is:

1. Updated values in C19 and C22
2. Calculation refresh to generate a new unique value in C14
3. Populate the new unique value from cell C14 into the table in E58:O68

The calculation refresh is necessary to generate the unique values.

Thanks again for responding so fast, much appreciated!

 

[diddi]

i would consider a redesign in algorithm, or pass some of the work to VBA to reduce the load in cell calculations which are slow
if you know the critical place where you need to recalculate then you can leave the calculation manual as i had, and insert the line

Calculate

in the critical place to update everything just the one time

 

[chappy]

i would consider a redesign in algorithm, or pass some of the work to VBA to reduce the load in cell calculations which are slow
if you know the critical place where you need to recalculate then you can leave the calculation manual as i had, and insert the line

Calculate

in the critical place to update everything just the one time

diddi

Thanks for the response. Switching to manual calc and inserting Calculate after each update in the i to n loop has improved the speed by 4 times at first glance.

Many thanks for your help, very much appreciated!

 

[diddi]

great to hear!