RE: can you print format multiple sheets in excel at once?

  • Thread starter Thread starter Luke M
  • Start date Start date
L

Luke M

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.
 
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.
 
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
 
UNBELIEVABLE! THANK YOU SO MUCH! I don't know why MS doesn't have this
feature built in, but this works great!
 
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.
 
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
 
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 upthe
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
Click to expand...
        .BlackAndWhite = Sheets(StartSheet).PageSetup.BlackAndWhite
Click to expand...
        .Zoom = Sheets(StartSheet).PageSetup.Zoom
        .PrintErrors = Sheets(StartSheet).PageSetup.PrintErrors
    End With
    End If
Next
End Sub
Click to expand...


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
 
Back
Top