Automatic calculation in macro

[chris_huh]

I've got a macro that changes a value of a cell and then creates a pdf of two selected sheets. By changing the cell's value a different set of data is pulled into the two spreadsheets.

Since there is a lot of data this takes a long time to complete. Would turning the calculation options to manual and then back to automatic speed things up?

The code i have is:

(i have just put the Application.Calculation = xlCalculationAutomatic line in as I was finding the sheet would sometimes open in manual mode, preventing anything from working properly)

Sub CreatePDFs()
'
' CreatePDFs Macro
'

'
Application.Calculation = xlCalculationAutomatic

Range("C8:C15").Value = ""

Sheets("1MonthlyProfile").Range("B3").Value = "Electricity"

Dim rng As Range
Dim cll As Range
Dim filepath As String

Set rng = Range("ElecBuildings")
filepath = ThisWorkbook.Path & "\" & Sheets("1MonthlyProfile").Range("AE3").Text

If Len(Dir(filepath, vbDirectory)) = 0 Then
    MkDir filepath
End If


For Each cll In rng

    Sheets("1MonthlyProfile").Range("C1").Value = cll.Value
    Sheets(Array("1MonthlyProfile", "1hrratekwhprfilmth")).Select
    Sheets("1MonthlyProfile").Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        filepath & "\" & Sheets("1MonthlyProfile").Cells(5, 31).Value & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
        
Next cll

    Sheets("1MonthlyProfile").Range("B3").Value = "Gas"

Dim rng2 As Range
Dim cll2 As Range

Set rng2 = Range("GasBuildings")

For Each cll2 In rng2

    Sheets("1MonthlyProfile").Range("C1").Value = cll2.Value
    Sheets(Array("1MonthlyProfile", "1hrratekwhprfilmth")).Select
    Sheets("1MonthlyProfile").Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        filepath & "\" & Sheets("1MonthlyProfile").Cells(5, 31).Value & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
        
Next cll2

End Sub

Thanks

 

[shadow12345]

I always put... at the start of every macro then turn it on again if needed. Its never hurt to try and do it quicker for me.

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

*Edit: Although I don't see much calculating being done so it might not make that much difference for you.

 

[chris_huh]

Would i put that code in place of the Application.Calculation = xlCalculationAutomatic line?

All the calculations are done by formulae in the sheets, rather than in the macro. Because of this i guess that the calculations are being done within the "For Each cll In rng" section. I need the sheets to be set to automatic at all other times though, so i need to make sure it is set to automatic when its opened and then set to automatic again after any macros are run.