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!
Bookmarks