none
Speed up macro RRS feed

  • Question

  • Hello Guys,

    How to speed up below macro?

    'Option Explicit

    Sub Update_Headers()
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.DisplayStatusBar = False
        Application.Calculation = xlCalculationManual
        ActiveSheet.DisplayPageBreaks = False

        Dim ws As Worksheet
        For Each ws In Worksheets
            If IsNumeric(Left(ws.Name, 2)) Then
                ws.PageSetup.LeftHeader = Sheet2.Range("B2") & vbLf & Sheet2.Range("B25")

                PageNum = CInt(Left(Trim(ws.Name), 2))
                If PageNum = 17 Or PageNum = 20 Then
                    Txt = Left(ws.Name, 4)
                Else
                    Txt = Left(ws.Name, 2)
                End If

                ws.PageSetup.CenterFooter = "Page " & Txt & " of 44"
            End If
        Next ws

        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        Application.DisplayStatusBar = True
        ActiveSheet.DisplayPageBreaks = True

        MsgBox "Headers updated successfully", vbInformation
    End Sub


    Dhaval Paun

    Tuesday, July 11, 2017 2:57 PM

All replies

  • Try adding

    Application.PrintCommunication = False

    to the first block of code, and

    Application.PrintCommunication = True

    to the last block.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, July 11, 2017 6:10 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, July 12, 2017 2:12 AM