can you print format multiple sheets in excel at once?
 

I would really like to know if there's a way to format multiple excel
worksheets for printing at once. That is, highlight all the sheets and set
print area, print titles, margins, layout, etc so that you only have to do
this once rather than 20 times for 20 worksheets. I cannot believe that
Excel still doesn't allow this....does it?

can you print format multiple sheets in excel at once?
 

Use Ctrl to select sheets individually, or Shift to select groups of sheets.
Then go to File - Page setup.
Change settings as desired.

Note that this still won't allow you to setup custom print areas/repeating
rows. You could prb do this with a Do...Loop command in VBA however.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Rigatoni" wrote:

I would really like to know if there's a way to format multiple excel
worksheets for printing at once. That is, highlight all the sheets and set
print area, print titles, margins, layout, etc so that you only have to do
this once rather than 20 times for 20 worksheets. I cannot believe that
Excel still doesn't allow this....does it?

can you print format multiple sheets in excel at once?
 

I have to solve this, so any info on the do..loop command would be
appreciated! I have to get to the point where I can set the print area, make
margin changes (preferably in print preview), add titles, etc so that all
sheets are done at one time. I spend hours formatting that could be done in
minutes.

"Luke M" wrote:

Use Ctrl to select sheets individually, or Shift to select groups of sheets.
Then go to File - Page setup.
Change settings as desired.

Note that this still won't allow you to setup custom print areas/repeating
rows. You could prb do this with a Do...Loop command in VBA however.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Rigatoni" wrote:

I would really like to know if there's a way to format multiple excel
worksheets for printing at once. That is, highlight all the sheets and set
print area, print titles, margins, layout, etc so that you only have to do
this once rather than 20 times for 20 worksheets. I cannot believe that
Excel still doesn't allow this....does it?

can you print format multiple sheets in excel at once?
 

I believe this sub will copy the majority of the page settings. Open VBE
(Alt+F11), Insert - Module. Paste the following in. Now, after setting up the
print area, margins, etc. on one sheet, run this macro (Alt+F8, choose macro,
run). As a note, on my machine, this took about 10 secs per sheet.

Sub FormatCopy()

StartSheet = ActiveSheet.Name

For Each ws In ThisWorkbook.Worksheets
If ws.Name < StartSheet Then

With ws.PageSetup
.PrintArea = Sheets(StartSheet).PageSetup.PrintArea
.PrintTitleRows = Sheets(StartSheet).PageSetup.PrintTitleRows
.PrintTitleColumns = Sheets(StartSheet).PageSetup.PrintTitleColumns
.LeftHeader = Sheets(StartSheet).PageSetup.LeftHeader
.CenterHeader = Sheets(StartSheet).PageSetup.CenterHeader
.RightHeader = Sheets(StartSheet).PageSetup.RightHeader
.LeftFooter = Sheets(StartSheet).PageSetup.LeftFooter
.CenterFooter = Sheets(StartSheet).PageSetup.CenterFooter
.RightFooter = Sheets(StartSheet).PageSetup.RightFooter
.LeftMargin = Sheets(StartSheet).PageSetup.LeftMargin
.RightMargin = Sheets(StartSheet).PageSetup.RightMargin
.TopMargin = Sheets(StartSheet).PageSetup.TopMargin
.BottomMargin = Sheets(StartSheet).PageSetup.BottomMargin
.HeaderMargin = Sheets(StartSheet).PageSetup.HeaderMargin
.FooterMargin = Sheets(StartSheet).PageSetup.FooterMargin
.PrintHeadings = Sheets(StartSheet).PageSetup.PrintHeadings
.PrintGridlines = Sheets(StartSheet).PageSetup.PrintGridlines
.PrintComments = Sheets(StartSheet).PageSetup.PrintComments
.PrintQuality = Sheets(StartSheet).PageSetup.PrintQuality
.CenterHorizontally = Sheets(StartSheet).PageSetup.CenterHorizontally
.CenterVertically = Sheets(StartSheet).PageSetup.CenterVertically
.Orientation = Sheets(StartSheet).PageSetup.Orientation
.Draft = Sheets(StartSheet).PageSetup.Draft
.PaperSize = Sheets(StartSheet).PageSetup.PaperSize
.FirstPageNumber = Sheets(StartSheet).PageSetup.FirstPageNumber
.Order = Sheets(StartSheet).PageSetup.Order
.BlackAndWhite = Sheets(StartSheet).PageSetup.BlackAndWhite
.Zoom = Sheets(StartSheet).PageSetup.Zoom
.PrintErrors = Sheets(StartSheet).PageSetup.PrintErrors
End With
End If
Next
End Sub



--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Rigatoni" wrote:

I have to solve this, so any info on the do..loop command would be
appreciated! I have to get to the point where I can set the print area, make
margin changes (preferably in print preview), add titles, etc so that all
sheets are done at one time. I spend hours formatting that could be done in
minutes.

"Luke M" wrote:

Use Ctrl to select sheets individually, or Shift to select groups of sheets.
Then go to File - Page setup.
Change settings as desired.

Note that this still won't allow you to setup custom print areas/repeating
rows. You could prb do this with a Do...Loop command in VBA however.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Rigatoni" wrote:

I would really like to know if there's a way to format multiple excel
worksheets for printing at once. That is, highlight all the sheets and set
print area, print titles, margins, layout, etc so that you only have to do
this once rather than 20 times for 20 worksheets. I cannot believe that
Excel still doesn't allow this....does it?

can you print format multiple sheets in excel at once?
 

UNBELIEVABLE! THANK YOU SO MUCH! I don't know why MS doesn't have this
feature built in, but this works great!

"Luke M" wrote:

I believe this sub will copy the majority of the page settings. Open VBE
(Alt+F11), Insert - Module. Paste the following in. Now, after setting up the
print area, margins, etc. on one sheet, run this macro (Alt+F8, choose macro,
run). As a note, on my machine, this took about 10 secs per sheet.

Sub FormatCopy()

StartSheet = ActiveSheet.Name

For Each ws In ThisWorkbook.Worksheets
If ws.Name < StartSheet Then

With ws.PageSetup
.PrintArea = Sheets(StartSheet).PageSetup.PrintArea
.PrintTitleRows = Sheets(StartSheet).PageSetup.PrintTitleRows
.PrintTitleColumns = Sheets(StartSheet).PageSetup.PrintTitleColumns
.LeftHeader = Sheets(StartSheet).PageSetup.LeftHeader
.CenterHeader = Sheets(StartSheet).PageSetup.CenterHeader
.RightHeader = Sheets(StartSheet).PageSetup.RightHeader
.LeftFooter = Sheets(StartSheet).PageSetup.LeftFooter
.CenterFooter = Sheets(StartSheet).PageSetup.CenterFooter
.RightFooter = Sheets(StartSheet).PageSetup.RightFooter
.LeftMargin = Sheets(StartSheet).PageSetup.LeftMargin
.RightMargin = Sheets(StartSheet).PageSetup.RightMargin
.TopMargin = Sheets(StartSheet).PageSetup.TopMargin
.BottomMargin = Sheets(StartSheet).PageSetup.BottomMargin
.HeaderMargin = Sheets(StartSheet).PageSetup.HeaderMargin
.FooterMargin = Sheets(StartSheet).PageSetup.FooterMargin
.PrintHeadings = Sheets(StartSheet).PageSetup.PrintHeadings
.PrintGridlines = Sheets(StartSheet).PageSetup.PrintGridlines
.PrintComments = Sheets(StartSheet).PageSetup.PrintComments
.PrintQuality = Sheets(StartSheet).PageSetup.PrintQuality
.CenterHorizontally = Sheets(StartSheet).PageSetup.CenterHorizontally
.CenterVertically = Sheets(StartSheet).PageSetup.CenterVertically
.Orientation = Sheets(StartSheet).PageSetup.Orientation
.Draft = Sheets(StartSheet).PageSetup.Draft
.PaperSize = Sheets(StartSheet).PageSetup.PaperSize
.FirstPageNumber = Sheets(StartSheet).PageSetup.FirstPageNumber
.Order = Sheets(StartSheet).PageSetup.Order
.BlackAndWhite = Sheets(StartSheet).PageSetup.BlackAndWhite
.Zoom = Sheets(StartSheet).PageSetup.Zoom
.PrintErrors = Sheets(StartSheet).PageSetup.PrintErrors
End With
End If
Next
End Sub



--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Rigatoni" wrote:

I have to solve this, so any info on the do..loop command would be
appreciated! I have to get to the point where I can set the print area, make
margin changes (preferably in print preview), add titles, etc so that all
sheets are done at one time. I spend hours formatting that could be done in
minutes.

"Luke M" wrote:

Use Ctrl to select sheets individually, or Shift to select groups of sheets.
Then go to File - Page setup.
Change settings as desired.

Note that this still won't allow you to setup custom print areas/repeating
rows. You could prb do this with a Do...Loop command in VBA however.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Rigatoni" wrote:

I would really like to know if there's a way to format multiple excel
worksheets for printing at once. That is, highlight all the sheets and set
print area, print titles, margins, layout, etc so that you only have to do
this once rather than 20 times for 20 worksheets. I cannot believe that
Excel still doesn't allow this....does it?

can you print format multiple sheets in excel at once?
 

hello

how can I set up macro for different page setup.
For ex, i have 1 ws that have different left/right margin then the 2 ws.
I set up my macro, but when I run it, the page set-up is going back to the
original.


--
Thanks


"Rigatoni" wrote:

UNBELIEVABLE! THANK YOU SO MUCH! I don't know why MS doesn't have this
feature built in, but this works great!

"Luke M" wrote:

I believe this sub will copy the majority of the page settings. Open VBE
(Alt+F11), Insert - Module. Paste the following in. Now, after setting up the
print area, margins, etc. on one sheet, run this macro (Alt+F8, choose macro,
run). As a note, on my machine, this took about 10 secs per sheet.

Sub FormatCopy()

StartSheet = ActiveSheet.Name

For Each ws In ThisWorkbook.Worksheets
If ws.Name < StartSheet Then

With ws.PageSetup
.PrintArea = Sheets(StartSheet).PageSetup.PrintArea
.PrintTitleRows = Sheets(StartSheet).PageSetup.PrintTitleRows
.PrintTitleColumns = Sheets(StartSheet).PageSetup.PrintTitleColumns
.LeftHeader = Sheets(StartSheet).PageSetup.LeftHeader
.CenterHeader = Sheets(StartSheet).PageSetup.CenterHeader
.RightHeader = Sheets(StartSheet).PageSetup.RightHeader
.LeftFooter = Sheets(StartSheet).PageSetup.LeftFooter
.CenterFooter = Sheets(StartSheet).PageSetup.CenterFooter
.RightFooter = Sheets(StartSheet).PageSetup.RightFooter
.LeftMargin = Sheets(StartSheet).PageSetup.LeftMargin
.RightMargin = Sheets(StartSheet).PageSetup.RightMargin
.TopMargin = Sheets(StartSheet).PageSetup.TopMargin
.BottomMargin = Sheets(StartSheet).PageSetup.BottomMargin
.HeaderMargin = Sheets(StartSheet).PageSetup.HeaderMargin
.FooterMargin = Sheets(StartSheet).PageSetup.FooterMargin
.PrintHeadings = Sheets(StartSheet).PageSetup.PrintHeadings
.PrintGridlines = Sheets(StartSheet).PageSetup.PrintGridlines
.PrintComments = Sheets(StartSheet).PageSetup.PrintComments
.PrintQuality = Sheets(StartSheet).PageSetup.PrintQuality
.CenterHorizontally = Sheets(StartSheet).PageSetup.CenterHorizontally
.CenterVertically = Sheets(StartSheet).PageSetup.CenterVertically
.Orientation = Sheets(StartSheet).PageSetup.Orientation
.Draft = Sheets(StartSheet).PageSetup.Draft
.PaperSize = Sheets(StartSheet).PageSetup.PaperSize
.FirstPageNumber = Sheets(StartSheet).PageSetup.FirstPageNumber
.Order = Sheets(StartSheet).PageSetup.Order
.BlackAndWhite = Sheets(StartSheet).PageSetup.BlackAndWhite
.Zoom = Sheets(StartSheet).PageSetup.Zoom
.PrintErrors = Sheets(StartSheet).PageSetup.PrintErrors
End With
End If
Next
End Sub



--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Rigatoni" wrote:

I have to solve this, so any info on the do..loop command would be
appreciated! I have to get to the point where I can set the print area, make
margin changes (preferably in print preview), add titles, etc so that all
sheets are done at one time. I spend hours formatting that could be done in
minutes.

"Luke M" wrote:

Use Ctrl to select sheets individually, or Shift to select groups of sheets.
Then go to File - Page setup.
Change settings as desired.

Note that this still won't allow you to setup custom print areas/repeating
rows. You could prb do this with a Do...Loop command in VBA however.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Rigatoni" wrote:

I would really like to know if there's a way to format multiple excel
worksheets for printing at once. That is, highlight all the sheets and set
print area, print titles, margins, layout, etc so that you only have to do
this once rather than 20 times for 20 worksheets. I cannot believe that
Excel still doesn't allow this....does it?

can you print format multiple sheets in excel at once?
 

You need to loop through the sheets and set up each sheet or sheets as you
want.

Basically creating two different print jobs.

One for sheet1 and one for sheet2 and 3


Gord Dibben MS Excel MVP

On Tue, 28 Jul 2009 11:24:01 -0700, user
wrote:

hello

how can I set up macro for different page setup.
For ex, i have 1 ws that have different left/right margin then the 2 ws.
I set up my macro, but when I run it, the page set-up is going back to the
original.

can you print format multiple sheets in excel at once?
 

On Jul 24, 9:03*pm, Luke M wrote:
I believe this sub will copy the majority of the page settings. Open VBE
(Alt+F11), Insert - Module. Paste the following in. Now, after setting up the
print area, margins, etc. on one sheet, run this macro (Alt+F8, choose macro,
run). As a note, on my machine, this took about 10 secs per sheet.

Sub FormatCopy()

StartSheet = ActiveSheet.Name

For Each ws In ThisWorkbook.Worksheets
* * If ws.Name < StartSheet Then

* * With ws.PageSetup
* * * * .PrintArea = Sheets(StartSheet).PageSetup.PrintArea
* * * * .PrintTitleRows = Sheets(StartSheet).PageSetup.PrintTitleRows
* * * * .PrintTitleColumns = Sheets(StartSheet).PageSetup.PrintTitleColumns
* * * * .LeftHeader = Sheets(StartSheet).PageSetup.LeftHeader
* * * * .CenterHeader = Sheets(StartSheet).PageSetup.CenterHeader
* * * * .RightHeader = Sheets(StartSheet).PageSetup.RightHeader
* * * * .LeftFooter = Sheets(StartSheet).PageSetup.LeftFooter
* * * * .CenterFooter = Sheets(StartSheet).PageSetup.CenterFooter
* * * * .RightFooter = Sheets(StartSheet).PageSetup.RightFooter
* * * * .LeftMargin = Sheets(StartSheet).PageSetup.LeftMargin
* * * * .RightMargin = Sheets(StartSheet).PageSetup.RightMargin
* * * * .TopMargin = Sheets(StartSheet).PageSetup.TopMargin
* * * * .BottomMargin = Sheets(StartSheet).PageSetup.BottomMargin
* * * * .HeaderMargin = Sheets(StartSheet).PageSetup.HeaderMargin
* * * * .FooterMargin = Sheets(StartSheet).PageSetup.FooterMargin
* * * * .PrintHeadings = Sheets(StartSheet).PageSetup.PrintHeadings
* * * * .PrintGridlines = Sheets(StartSheet).PageSetup.PrintGridlines
* * * * .PrintComments = Sheets(StartSheet).PageSetup.PrintComments
* * * * .PrintQuality = Sheets(StartSheet).PageSetup.PrintQuality
* * * * .CenterHorizontally = Sheets(StartSheet).PageSetup.CenterHorizontally
* * * * .CenterVertically = Sheets(StartSheet).PageSetup.CenterVertically
* * * * .Orientation = Sheets(StartSheet).PageSetup.Orientation
* * * * .Draft = Sheets(StartSheet).PageSetup.Draft
* * * * .PaperSize = Sheets(StartSheet).PageSetup.PaperSize
* * * * .FirstPageNumber = Sheets(StartSheet).PageSetup.FirstPageNumber
* * * * .Order = Sheets(StartSheet).PageSetup.Order

* * * * .BlackAndWhite = Sheets(StartSheet).PageSetup.BlackAndWhite

* * * * .Zoom = Sheets(StartSheet).PageSetup.Zoom
* * * * .PrintErrors = Sheets(StartSheet).PageSetup.PrintErrors
* * End With
* * End If
Next
End Sub



Hi Luke M,

Is there an option to change your script to print the worksheets in
colour?

I'm having a similar issue but when trying to print all the worksheets
in colour.

K