Print multiple selections on one page

[Bruno]

I want to print several selected area's on only one page. Excel can print selected cells, but excel prints every selection on a different page...
Lotus 1-2-3 can do it, so why not in XL ?

[Dave Hawley]

Hi Bruno

Try some code along this line.

Sub PrintSetUp()
Dim i As Integer
Dim wsSheetStart As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wsSheetStart = ActiveSheet

Sheets.Add().Name = "MyPrintSheet"
If ActiveSheet.Name <> "MyPrintSheet" Then ActiveSheet.Delete
Application.DisplayAlerts = True

wsSheetStart.Select
Set wsSheetStart = Nothing

For i = 1 To Selection.Areas.Count
Selection.Areas(i).Copy Destination:= _
Sheets("MyPrintSheet").Range("A65536").End(xlUp).Offset(2, 1)
Next i

Application.ScreenUpdating = True
End Sub


Should set it up ready for printing, you may need to tweek it a bit to suit

[Bruno]

Hi Dave, I was already thinking at some code like yours... I don't think it's possible without vba.

I have some problems with your code :
- the selections are copied one above the other, starting at cell B3, so instead of Offset(2, 1) there must be something to count the lines of the selection

- the ColumnWidth and RowHeight in the sheet 'MyPrintSheet' are not like the original cells

- for the cells where formula's are used, i get a error (solution : paste only values ?)

[Bruno]

The first problem is already solved with this code :

wsSheetStart.Select

Set wsSheetStart = Nothing

RowsUsed = 0 

For i = 1 To Selection.Areas.Count

    Selection.Areas(i).Copy Destination:= _

    Sheets("MyPrintSheet").Range("A1").Offset(RowsUsed, 0)

    RowsUsed = RowsUsed + Selection.Areas(i).Rows.Count

Next i

only two problems to go...