Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Printing non-contiguous areas on one page

Insert a new worksheet (you're going to use this only for printing). Do this for each area you want to print on the same page, the areas don’t have to be on the same sheet. If you change the original range, the picture will change too (values and formatting!). You can always use this sheet to print because it will always update if you change the original data.


Excel 97-2003

Select the range
Edit | Copy in the menu bar (or use Ctrl-C)
Go to the new worksheet and with the Shift key pressed click on Edit | Paste Picture Link.
Go back and do the same for the other areas.

Note: You see that the edit menu will change when you press the shift button when you click on Edit.


Excel 2007-2013

Select the range
Home tab | Clipboard group | Copy (or use Ctrl-C)
Go to the new worksheet and Click on the arrow on the Paste button on the Home Tab

Excel 2007: Choose As Picture >Paste Picture Link
Excel 2010-2013: Choose the Linked picture button.

Go back and do the same for the other areas.


Print selection or range with one or more areas with a macro

The macro will add a new sheet and copy all the selection areas on it. Then it will print and delete the sheet. You can also enter the more areas range in the code if you want.

Sub Test()
    Dim Destrange As Range
    Dim Smallrng As Range
    Dim Newsh As Worksheet
    Dim Ash As Worksheet
    Dim Lr As Long

    Application.ScreenUpdating = False

    Set Ash = ActiveSheet
    Set Newsh = Worksheets.Add

    Lr = 1

    'You can also use a range with more areas like this
    'For Each smallrng In Ash.Range("A1:C1,D10:G20,A30").Areas

    For Each Smallrng In Selection.Areas
        Set Destrange = Newsh.Cells(Lr, 1)
        Destrange.PasteSpecial xlPasteValues
        Destrange.PasteSpecial xlPasteFormats
        Lr = Lr + Smallrng.Rows.Count
    Next Smallrng



    Application.DisplayAlerts = False
    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

End Sub