Using VBA to set Headers and Footers with Font and Fontsize parameters

[Android]

I'm using the following code to automatically add headers and footers for
all the sheets in my workbooks.

Although it works, it is extraordinarily slow -- 2-3 minutes.

Can anyone suggest a way to do this faster. I am using Office XP, under
Windows 2000 Pro operating system.

The code I am using is:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sCount, i As Integer
sCount = Worksheets.Count
For i = 1 To sCount
With Worksheets(i).PageSetup
.LeftHeader = "&""Arial""&8" & "Worksheet: &A"
.LeftFooter = "&""Arial""&8" & ThisWorkbook.FullName & Chr(10) &
" " & Chr(10) & " "
.CenterFooter = "&""Arial""&8" & "Page &P of &N"
.RightFooter = "&""Arial""&8" & "Print Date: &D"
End With
Next i
End Sub

 

[Bob Phillips]

Android,

Rather than do it for every worksheet, just do it for the activesheet. It is
not necessary to do all of them.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Android]

My mistake. Please ignore this question. Looks like it was because I had
numerous windows open. Problem went away with a simple re-boot.

 

[keepitcool]

If you use excel4macro all attribs can be set at once.

ExecuteExcel4Macro "PAGE.SETUP("""",""&L&9&D &T&C&9&A&R&9" & "Page" & "
&P " & "of" & " &N"",0.6,0.4,0.4,0.6,0,0,0,0,1,,100,
#N/A,1,0,,0.2,0.3,0,0)"


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[JE McGimpsey]

At least do it for the selected sheets if multiple sheets are grouped
(and the activesheet if not)...

Dim wsSheet As Worksheet
For Each wsSheet In ActiveWindow.SelectedSheets
With wsSheet.PageSetup
'Do stuff
End With
Next wsSheet

 

[Android]

Although the problem turned out to be different than I thought at first, the
responses are very useful in increasing effeciency for large project.

Thank you all very much.

Android.

 

[Android]

I don't see "ExecuteExcel4Macro" on the Office 2000 and Office XP that I
have access to. Is there a way to enable it?

 

[keepitcool]

it's a standard method in vba,i dont think it can be disabled.
probably you'll need to prefix it with application

application.executeExcel4Macro

been there since xl97 and still is in office2003

for syntax you'll need to search for a copy of MACROFUN.HLP on the net
there's lot's of usefull stuff you can do with the old macrocommands.

PageSetup for a single page, but with lots of settings
will take several seconds with VBA (or longer on large sheets)
With xl4 it goes in 0.001 secs...



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >