I have an Excel Workbook that someone added some coding to a long time ago. This code runs extremely slow and hangs every machine up for 5 seconds or so when it runs. When auto-save is enabled in Excel, the file becomes almost unusable because of this. The only thing I have modified is adding the ScreenUpdating stuff to the beginning and end of each sub, but that had little to no effect. Here is the code:

Sub Worksheet_Footer()

Application.ScreenUpdating = False

For i = 1 To Worksheets.Count
doLayout (i)
Next i

Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name <> "Cover Sheet" Then
            With ws.PageSetup
                .RightHeader = ""
                .LeftHeader = ""
                .LeftFooter = ""
                .RightFooter = ""
            End With
        End If
    Next ws

Application.ScreenUpdating = True

End Sub
      

Function doLayout(Index As Integer)

Application.ScreenUpdating = False

With Worksheets(Index).PageSetup
.LeftHeader = "&""Century Gothic""&12" & Chr(13) & Sheet1.Range("$B$4") & Space(1) & Sheet1.Range("$B$5") & Space(1) & Sheet1.Range("$B$6") & Space(1) & ":" & Space(1) & Sheet1.Range("$B$7")
.LeftFooter = "&""Century Gothic""&8" & "&B Rev: &B" & Sheet1.Range("$B$8") & Space(1) & "-" & Space(1) & Sheet1.Range("$B$9") & Space(5) & "&B Designer: &B" & Space(1) & Sheet1.Range("$B$2").Value & Space(5) & "&B Phone: &B" & Space(1) & "(972) 392-3202" & Space(5) & "&B Security:&B" & Space(1) & "TX B14470 | TX ACR-1776026 | AR CMPY.0002197" & b & Space(5) & "&B Printed: &B" & Space(1) & DateValue(Now)
.RightFooter = "&""Century Gothic""&8" & "Page &P of &N"
End With

Application.ScreenUpdating = True

End Function
Any ideas for how we can get this to run a little smoother/faster?? Thanks in advance for the help!