Printing with macros

Location: http://www.mvps.org/dmcritchie/excel/printing.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

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.

Printing Selected Range per sheet (#range)

 AB
1Sheet1A1:B9
2Sheet2C8:D91
3Sheet18D17: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

Printing the active sheet in reverse order (#reverseorder)

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

Printing to same file each time   (#samefile)

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

Problems

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.
This page was introduced on July 25, 2000. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP] 

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved