| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Print Multiple Sheets to PDF Problem | Excel Discussion (Misc queries) | |||
| Consistent print settings across multiple sheets | Excel Discussion (Misc queries) | |||
| Print 1st Page Multiple Sheets in Same Workbook | Excel Discussion (Misc queries) | |||
| how to print multiple work sheets using vba | Excel Discussion (Misc queries) | |||
| Print multiple excel sheets in color | Setting up and Configuration of Excel | |||
Linear Mode
