Print Formating using VBA
 

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

Print Formating using VBA
 

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

Ross wrote:

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

Print Formating using VBA
 

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

"Ross" skrev i meddelelsen
...
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

Print Formating using VBA
 

For the OP

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


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

Ross wrote:

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

Print Formating using VBA
 

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" wrote:

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

Ross wrote:

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

Print Formating using VBA
 

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

Ross wrote:

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" wrote:

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

Ross wrote:

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


--

Dave Peterson