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

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" wrote in message
...
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

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

In article ,
"Bob Phillips" wrote:

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

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.

"JE McGimpsey" wrote in message
...
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

In article ,
"Bob Phillips" wrote:

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

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


"Android" wrote in message
...
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

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


"Android" wrote:

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

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" wrote in message
...
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


"Android" wrote:

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

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


"Android" wrote:

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" wrote in message
...
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


"Android" wrote:

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