| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Page Setup on selected worksheets
I have the following code which produced error:
Sub formatallselected() Dim Sh As Worksheet For Each Sh In activeworkbooks.SelectedSheets With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$AA$35" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&A" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With Next End Sub Can someone kind enough to help? Any comment is much appreciated. TIA |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Page Setup on selected worksheets
Sub formatallselected()
Dim Sh As Worksheet For Each Sh In activeWindow.SelectedSheets With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$AA$35" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&A" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With Next End Sub -- Regards, Tom Ogilvy "Agustus" wrote in message oups.com... I have the following code which produced error: Sub formatallselected() Dim Sh As Worksheet For Each Sh In activeworkbooks.SelectedSheets With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$AA$35" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&A" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With Next End Sub Can someone kind enough to help? Any comment is much appreciated. TIA |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Page Setup on selected worksheets
Try
For Each Sh In ActiveWindow.SelectedSheets Sh.Activate Read also this thread from John Green PageSetup in VBA has always been a painfully slow process. If you can't avoid having to set these parameters, you can use the Excel 4 macro function, PAGE.SETUP to carry out most of the PageSetup operations much more quickly. The following two macros are almost equivalent, and should give you the clues you need to start using PAGE.SETUP. You can download a full description of all the Excel 4 macro functions from Microsoft's web site: Sub PS() ActiveSheet.DisplayPageBreaks = False With ActiveSheet.PageSetup .LeftHeader = "My Company" .CenterHeader = "" .RightHeader = "&D / &T" .LeftFooter = "Highly Confidential and Proprietary" .CenterFooter = "" .RightFooter = "Finance" .LeftMargin = Application.InchesToPoints(0.54) .RightMargin = Application.InchesToPoints(0.3) .TopMargin = Application.InchesToPoints(0.4) .BottomMargin = Application.InchesToPoints(0.36) .HeaderMargin = Application.InchesToPoints(0.22) .FooterMargin = Application.InchesToPoints(0.17) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments ' .PrintQuality = 600 ' does not work with all the printers .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 End With End Sub Sub PS4() head = """&LMy Company&R&D / &T""" foot = """&LHighly Confidential and Proprietary&RFinance""" pLeft = 0.54 pRight = 0.3 Top = 0.4 bot = 0.36 head_margin = 0.22 foot_margin = 0.17 hdng = False grid = False notes = False quality = "" h_cntr = False v_cntr = False orient = 2 Draft = False paper_size = 1 pg_num = """Auto""" pg_order = 1 bw_cells = False pscale = True pSetUp = "PAGE.SETUP(" & head & "," & foot & "," & pLeft & "," & pRight & "," pSetUp = pSetUp & Top & "," & bot & "," & hdng & "," & grid & "," & h_cntr & "," pSetUp = pSetUp & v_cntr & "," & orient & "," & paper_size & "," & pscale & "," pSetUp = pSetUp & pg_num & "," & pg_order & "," & bw_cells & "," & quality & "," pSetUp = pSetUp & head_margin & "," & foot_margin & "," & notes & "," & Draft & ")" Application.ExecuteExcel4Macro pSetUp End Sub John Green (Excel MVP) Sydney Australia -- Regards Ron de Bruin http://www.rondebruin.nl "Agustus" wrote in message oups.com... I have the following code which produced error: Sub formatallselected() Dim Sh As Worksheet For Each Sh In activeworkbooks.SelectedSheets With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$AA$35" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&A" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With Next End Sub Can someone kind enough to help? Any comment is much appreciated. TIA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get the Page Setup to apply to all the worksheets? | Excel Discussion (Misc queries) | |||
| changing the page setup for multiple worksheets in a workbook | Excel Discussion (Misc queries) | |||
| Page Setup Across Multiple Worksheets | Excel Worksheet Functions | |||
| Page Setup for multiple worksheets macro problem | Excel Discussion (Misc queries) | |||
| How can I copy page setup settings to other worksheets? Thanks | Excel Discussion (Misc queries) | |||
Linear Mode
