Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Filtering Columns for Unique Values
Printing Multiple Worksheets on a Single Page
If you have a large worksheet, you may want to print it out in "parts" and automatically vary the information contained in the header or footer of each part. There is no intrinsic way to do this in Excel; the best approach is a macro to do the following:
Notice that these steps require the use of named ranges. You could have a named range for each portion of the worksheet that you want to print, and a named range (which would be a single cell) that represents the header or footer information that you want for each print area. The following macro will implement the above steps:
Sub PrintRegions()
Dim x As Integer
'Change the dimension of the arrays to equal the number
' of printing areas you have
Dim Region(4) As String
Dim Head(4) As String
'Fill this array with the names of the ranges to be printed
Region(1) = "North"
Region(2) = "South"
Region(3) = "East"
Region(4) = "West"
'Fill this array with the names of the ranges to be in the header
Head(1) = "NorthHead"
Head(2) = "SouthHead"
Head(3) = "EastHead"
Head(4) = "WestHead"
For x = 1 To UBound(Region)
ActiveSheet.PageSetup.PrintArea = Range(Region(x)).Address
ActiveSheet.PageSetup.LeftHeader = Range(Head(x)).Value
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Next
End Sub
This example prints out only four areas of a worksheet. These areas are named ranges: North, South, East, and West. Similarly, the named ranges—which are really single cells—used for the left portion of the headers are NorthHead, SouthHead, EastHead, and WestHead.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2058) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
Your Data, Your Way! Want the greatest control possible over how your data appears on the page? Excel's custom formats can provide that control, and ExcelTips: Custom Formats can unlock the secrets to creating your own custom formats.