VBA Express : Excel - Setup & Print All Sheets In A Workbook

Excel

Setup & Print All Sheets In A Workbook

Ease of Use

Intermediate

Version tested with

2000, 2003

Submitted by:

lucas

Description:

This routine is most useful if you have a workbook with worksheets consisting of one page each. Sets up each page to print including the used range on each sheet and prints them. Adds sheet X of X Sheets to footer. No hidden sheets or blank sheets are included in the count or the print job

Discussion:

Allow you to configure the headers and footers in all sheets in the workbook at one time. This is only set to intermediate because there is a lot of configuration possible in the code. I have a workbook where each sheet is basically a page and this allows me to add or move pages and then print the entire workbook without having to deal with the details more than one time....just fix it to suit in the code and it's ready to go. The macro is currently set to just preview each sheet instead of printing them but the directions are in the code to change it to print each sheet.

Code:

instructions for use

			Put everything below this line In a standard code module: 
Option Explicit 
Sub PrintAllSheets() 
     '
    Dim M As Long, N As Long, Firstsht As Long, Lastsht As Long, Sheet As Object 
     '
    Lastsht = Sheets.Count 
    M = 0: N = Lastsht 
     '
    For Each Sheet In Sheets 
        If Not Sheet.Visible Then N = N - 1 
        If Sheet.Visible And Sheet.Type = xlWorksheet Then 
            If WorksheetFunction.CountA(Sheet.UsedRange) = 0 Then 
                N = N - 1 
            End If 
        End If 
    Next 
     '
    For Firstsht = 1 To Lastsht 
         '
        If Sheets(Firstsht).Visible = True Then 
             '
            If Not TypeName(Sheets(Firstsht)) = "Chart" Then 
                If WorksheetFunction.CountA(Sheets(Firstsht).UsedRange) <> 0 Then 
                    M = M + 1 
                    GoSub DoPrint 
                End If 
            Else 'else it's a chart
                M = M + 1 
                GoSub DoPrint 
            End If 
             '
        End If 
         '
    Next 'Firstsht
    Exit Sub 
DoPrint: 
    With Sheets(Firstsht).PageSetup 
        .CenterHeader = "&F!&A" 
        .CenterFooter = "Page " & CStr(M) & " of " & N 
        .RightFooter = "?" & CStr(Year(Date)) 
        .FitToPagesWide = 1 
        .FitToPagesTall = 1 
    End With 
    With Application 
        .EnableEvents = False 
        Sheets(Firstsht).PrintPreview '.PrintOut
        .EnableEvents = True 
    End With 
    Return 
End Sub

How to use:

Open the Visual Basic Editor by going to tools-Macro's-Visual Basic Editor or use Alt-F11
On the toolbar of the Visual Basic Editor, go to insert - module
In the module pane paste the code above.
Close the Visual Basic Editor by clicking the X in the upper right corner or go to File-Close
Configure to suit your needs and then run it. It will start with the first worksheet in the workbook and print all sheets in the order they are in.

Test the code:

On the main menu go to tools-macro-macros.
In the dialog window select PrintBook and then click run.

Sample File:

PrintAllSheets.zip 16.24KB

Approved by mdmackillop

This entry has been viewed 369 times.