Excel.Tips.Net Welcome toExcel.Tips.Net

Helpful Links

Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment

Tips.Net Store

ExcelTips FAQ
ExcelTips Premium

Learn Access Now

Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips

Advertise on the
ExcelTips Site

Newest Tips

Assigning a Macro to a Keyboard Combination

Creating Scenarios

Using Message Boxes

Understanding Phantom Macros

Picking a Group of Cells

Running Out of Memory

Hiding Rows Based on a Cell Value

 

Printing Selected Worksheets

Summary: Need to print just a few worksheets out of a group of workbooks? There's no need to manually load each workbook and print the sheets; instead you can use the macro presented in this tip. It allows you to print selected worksheets from each workbook in a particular folder. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)

If you have a lot of workbooks that have accumulated over the years, you may have a need to print some of the worksheets out of each of them. For instance, you may need to print the second and third worksheets out of each workbook in a folder. Loading each workbook and then printing selected sheets could take a huge amount of time.

A quicker way is to create a macro that will do the printing for you. The following macro starts by asking you for a directory path. Provided that you specify a path, the macro then starts to load each XLS (Excel) file in the directory, and then print the second and third worksheet from each one. Once printed, the worksheet is closed.

Public Sub PrintWorkbooks()
    Dim sCurFile As String
    Dim sPath As String

    'Get the path
    sPath = InputBox("Starting path?", "PrintWorkbooks")
    If sPath <> "" Then
        On Error Resume Next
        Application.ScreenUpdating = False
        If Right(sPath, 1) <> "\" Then
            sPath = sPath & "\"
        End If
        sCurFile = Dir(sPath & "*.xls", vbNormal)
        Do While Len(sCurFile) <> 0
            Workbooks.Open sPath & sCurFile, , True
            With Workbooks(sCurFile)
                .Worksheets(2).PrintOut
                .Worksheets(3).PrintOut
                .Close SaveChanges:=False
            End With
            sCurFile = Dir
            DoEvents
        Loop
        Application.ScreenUpdating = True
        On Error GoTo 0
    End If
End Sub

Obviously, if you have quite a few workbooks in the directory, printing could take quite some time. You may want to find some time when you have nothing else to do, and then just let the macro start running.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2215) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Tame Your Data! ExcelTips: Filters and Filtering provides all the details necessary to let you manage large sets of data with confidence and ease. Its information-packed pages demonstrate how to use the two types of filters provided by Excel: AutoFilters and advanced filters.
 
Check out ExcelTips: Filters and Filtering today!