MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Print macro runs slowly

Posted by Mike H on January 21, 2002 6:44 AM

Sorry, I put this question on the Bulletin Board a few hours ago but no one posted a reply! Can anyone help?

I use a macro, code shown below, when printing, that puts the full filepath and filename and inserts page numbers into the footers of the prints.

The macro works perfectly but the problem is that it runs very slowly. It can take over 20 seconds for a workbook with a lot of sheets.

I wondered if anyone could see why it runs slowly and suggest improvements that would speed it up.

Many thanks

Mike H

Sub myfooter()
' Macro to format the footers in excel
Dim Filepath As String, Wks As Worksheet
Filepath = ActiveWorkbook.FullName
For Each Wks In Worksheets
With Wks.PageSetup
.LeftFooter = "&A" & Chr(10) & "&8" & Filepath
.CenterFooter = "Page &P of &N" & Chr(10) & " "
.RightFooter = "Printed at & &T" & Chr(10) & "Printed on &D"
End With
Next Wks

End Sub

Posted by Juan Pablo G. on January 21, 2002 8:15 AM

This is a problem with Excel. I've tried using XLM macros which can run more efficiently but didn't work with XL2000 or XP.

Juan Pablo G. Macro to format the footers in excel

Posted by bj on January 21, 2002 11:39 AM

I've added the following to the beginning of macros to speed them up - it helps a little with how fast they run.

Application.ScreenUpdating = False