Print Formating using VBA

[Ross]

Please help,

I am formatting each page to print using vba and it is very cool but slow
with 75 tabs / pages to format.

I can select all tabs (with vba) and manually format all selected tabs but
my "With Reference" only allows 1 tab / Page.

How can I do all the pages (.PageSetup) with one pass?

My Code:
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A"
..
..
..
..
..etc

What should I change "With ActiveSheet.PageSetup" to so that it can deal
with ALL SELECTED SHEETS instead of doing just one sheet even though all are
selected?

Many Thanks

Ross

 

[Dave Peterson]

Dim wks as worksheet
for each wks in activewindow.selected sheets
wks.activate
your code here
next wks

or

Dim wks as worksheet
for each wks in activeworkbook.worksheets 'to get all the sheets
wks.activate
your code here
next wks

 

[Per Jessen]

Hi Ross

You can not edit headers for all sheets at once. Try this instead:

For Each sh In ThisWorkbook.Sheets
With sh.PageSetup
.LeftHeader = ""
.CenterHeader = "&A"
End With
Next

Regards,
Per

 

[Ron de Bruin]

For the OP

See also this code from John Green for faster code
http://www.mcgimpsey.com/excel/udfs/pagesetup.html

 

[Ross]

Dave,

Thank you for your response:
To clerify, I have code that runs prior to the code pasted into this "Blog"
that correctly selects all of the sheets/tabs prior to running the
formatting code:

This runs to select the sheets:
'*********************
Sub Select_All_Sheets()
'*********************
Dim Sh As Worksheet
For Each Sh In ActiveWorkbook.Worksheets
Sh.Select False
Next Sh
End Sub

The problem is that when the code previously pasted runs, it "de-selects"
all of the previously selected sheets and formats only one active sheet and
not the 75 previously selected sheets.

That is why I think that "With ActiveSheet.PageSetup" only allows one sheet
at a time. AS previously described, I can stop the code before "With
ActiveSheet.PageSetup" and manually adjust all 75 sheets at once (say change
from portrait to landscape). I need code that will allow "With
ActiveSheet(S).PageSetup" (plural) instead of singular.

Does this make sense?

Thanks again!

Ross

 

[Dave Peterson]

It makes perfectly good sense. But doesn't my suggestion may sense, too?