View Single Post
  #2   Report Post  
Drew
 
Posts: n/a
Default

Hi there
I had exactly the same question a few months ago. The simple answer is
Excel can't do this easily.
So a workaround is required. What I did for my workbook was to add an
extra worksheet on which I copied what I wanted to print. I called the
worksheet "Double". The worksheet has these settings:
A4 Landscape 55% print size.

Here is the routine from my program:

Sub a5bProgReportPrinter()
Set Shr = Sheets("Progress"): Set Shd = Sheets("Data")
a5bEventsOff
w = 0 'P2 onto one Page flag
Sheets("Double").Range("A1:R59").Clear
For T = Shr.[M9] + 4 To Shr.[P9] + 4
a5bLoadProgDetails (T) 'Load details
Select Case Trim(Application.Caller)
Case "P1P" 'for printing only 1 page
Sheets("Progress").PrintOut From:=1, To:=1, copies:=1
Application.Wait Now + TimeValue("00:00:02")
Case "P2P" 'for printing 2 onto one page
w = w + 1
If w = 1 Then
Shr.Range("A1:H58").Copy Sheets("Double").[A1]
End If
If w 1 Then
w = 0
Shr.Range("A1:H58").Copy Sheets("Double").[J1]
Sheets("Double").PrintOut From:=1, To:=1, copies:=1
Sleep 500 'Time delay - don't overflow print buffer
Sheets("Double").Range("A1:R59").Clear
End If
End Select

Next T
If w = 1 Then Sheets("Double").PrintOut From:=1, To:=1, copies:=1
Shr.[A6].Select
a5bEventsOn
End Sub

Hope this helps.

Andrew Bourke