Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you set or reset the print range for several sheets at a ti
how do you set or reset the print range for several sheets at a time?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you set or reset the print range for several sheets at a ti
"Mestrella31" wrote in message ... how do you set or reset the print range for several sheets at a time? try something like this For Each s In ActiveWorkbook.Sheets s.PageSetup.PrintArea = "$A$1:$E$10" Next s /Fredrik |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you set or reset the print range for several sheets at a ti
grouping is largely not supported in VBA, however.
If you have one sheet set up, then KeepItCool has suggested this as a way to format multiple sheets: sheets(array("sheet2","sheet3","sheet4")).select sheets("sheet3").activate SendKeys "{enter}" Application.Dialogs(xlDialogPageSetup).Show keepITcool ------------------------- Other than that you would need to loop through the sheets. You are probably already aware that pagesetup is extremely slow. So you should only set those attributes that you need to do because each setting is an individual call to the slow pagesetup object. somewhat faster is to use the xl4 macro approach posted here by John Green: From: John Green ) Subject: Pagesetup code takes too long Newsgroups: microsoft.public.excel.programming View complete thread (5 articles) Date: 1999/03/29 Macro = "Page.Setup(,,.25,.25,.5,.25,,False,True,True,2,1, {1,1},,,,,.25,.25)" ExecuteExcel4Macro Macro HTH, John Green - Excel MVP Sydney Australia ================================= From: John Green ) Subject: About PageSetup.. Newsgroups: microsoft.public.excel.programming View complete thread (10 articles) Date: 2001-01-22 12:57:23 PST 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, Tom Ogilvy "Mestrella31" wrote in message ... how do you set or reset the print range for several sheets at a time? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel ~ print same range across sheets | Excel Discussion (Misc queries) | |||
x y scatter chart series ranges reset to x(range) = y(range) | Charts and Charting in Excel | |||
How do I add Watermark, across all the sheets in the print range, | Excel Worksheet Functions | |||
set or reset the print range for several sheets at a time? | Excel Discussion (Misc queries) | |||
Using For Next to reset Pge Brk Preview on all sheets | Excel Programming |