locked
Excel 2010 reset macro RRS feed

  • Question

  • I have made a heavily programmed and formatted attendance report form for my company.  The file size is 2.3 meg.  I have a reset macro that works but, due to the workbook's size, it's not very smooth and it takes about 13.5 seconds to complete a reset.  To develop my macro, I made a copy of the live attendance sheet and I simply copy and paste (formulas only) from the copy attendance form to the live form.  Is there a way to reset that is more instantaneous?

    Thanks,

    Estebanes

    Thursday, April 19, 2012 3:15 PM

Answers

  • Before you copy the formulas, turn off events and screen updating, and set the calculation mode to manual:

    Sub ResetYourBook()
    Dim xlCalc As XlCalculation
        With Application
            .ScreenUpdating = False
            xlCalc = .Calculation
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayAlerts = False
        End With

        'Your current code here

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .DisplayAlerts = True
            .Calculation = xlCalc
        End With
    End Sub


    HTH, Bernie


    Thursday, April 19, 2012 4:41 PM

All replies

  • Before you copy the formulas, turn off events and screen updating, and set the calculation mode to manual:

    Sub ResetYourBook()
    Dim xlCalc As XlCalculation
        With Application
            .ScreenUpdating = False
            xlCalc = .Calculation
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayAlerts = False
        End With

        'Your current code here

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .DisplayAlerts = True
            .Calculation = xlCalc
        End With
    End Sub


    HTH, Bernie


    Thursday, April 19, 2012 4:41 PM
  • Works great.  Thanks.
    Sunday, April 22, 2012 6:22 PM