Expanding the possibilites with Macros
|This page contains some VBA macros. If you need assistance to install or to use a macro please refer refer to my GetFormula page.|
A B 1 Sheet1 A1:B9 2 Sheet2 C8:D91 3 Sheet18 D17:K87 See posting by Nick Hodge, Microsoft MVP - Excel, 2000/07/23 in excel.printing that prints only the specified range for each worksheet listed.Sub Print_financials() Dim listRange As Range Dim myCell As Range Dim printRange As String On Error Resume Next Set listRange = ThisWorkbook.Worksheets("File_Maint").Range("Stmt-reports") With Application .ScreenUpdating = False For Each myCell In listRange printRange = myCell.Offset(0, 1).Value With Worksheets(myCell.Value) .PageSetup.PrintArea = printRange .PrintOut End With Next myCell .ScreenUpdating = True End With End Sub
Print Area must be limited for certain sheets (#printarea)The following example is a copy of that found in pathname.htm where you can find more information on symbols and other coding that you can use.
The situation is that there are some sheets with formulas in all of rows 2 through 3000, but the rows that should be included should only extend down to last entry in Column A. The PrintArea limits the print area it does not extend it so all 256 columns (columns.count) can be included.
If you think this situation actually applies, you should consider why there are formulas on rows that down't need them.Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = Application.ActiveWorkbook.FullName & " &A" .PageSetup.RightFooter = "&8 &D &T" If LCase(ActiveSheet.Name) = "sheet28" Then .PageSetup.PrintArea = .Cells(1, 1).Resize( _ .Range("A" & Rows.Count).End(xlUp).Row, Columns.count).Address End If End With
Some printers can print the pages in reverse order, so you should check your printer options first. If your printer prints on both sides (duplex) you are most likely to have additional options of this nature.
Printing spreadsheets in reverse order posted by Myrna Larson, Excel MVP, 2000-07-20 in misc.Sub ReversePrint() Dim NumPages As Long, Page As Long NumPages = ExecuteExcel4Macro("GET.DOCUMENT(50)") For Page = NumPages To 1 Step -1 ActiveSheet.PrintOut from:=Page, To:=Page Next Page End Sub
The following is based on a posted reply from Dave (Ford Motor Co.) and was noted that this only works in Excel 2000.Application.DisplayAlerts = False 'ignore file exists warning ActiveWindow.SelectedSheets.PrintOut Copies:=1, PrintToFile:=True, _ Collate :=True, PrToFileName:="c:\temp\test.prt" Application.DisplayAlerts = True 'restore alerts warnings
When I view my print preview all is fine but when I print the document it is reduced in Height and Width.
Printer settings: size of paper usually better letter (8.5 x 11) or A4.
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved