Print to papersize A3 or A4 to a printer with different trays

[Skagga5]

Hi

I have seen a similar threat on here posted in 2009 but unfortunately it doesnt quite answer the issue I am having.

I have done a userform which allows the user to select printing to each section(All, A, B or C) of the report to either paper size A3 or A4 looking something like below -
Print All A3 A4 (these buttons are option buttons)
Print A A3 A4
Prtint B A3 A4
Print C A3 A4

I have coded and part recorded a macro to change the paper size to either A3 or A4 depending on the user's selection.

The problem i have is if i take out the print command, it seems to change the paper sizes as it should but when i print it prints to the same size as the previous print i did, regardless of what was selected on the userform

.

I have a printer with multiple tray sizes, and since I need to change paper sizes, I assume i need to change trays as well.

Any help on this issue would be much appreciated.

Below is the two macros -

'''''''''''''Set Print area and print to A3'''''''''''''''''

Private Sub PrintAllA3_Click()<o></o>
Dim LastRow As Long<o></o>
Dim LastCol As Long<o></o>
<o></o>
With ActiveSheet<o></o>
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row<o></o>
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column<o></o>
End With<o></o>
<o></o>
Range(Cells(1, 1), Cells(LastRow, LastCol)).Select<o></o>
ActiveSheet.PageSetup.PrintArea = Selection<o></o>
<o></o>
Application.PrintCommunication = False<o></o>
With ActiveSheet.PageSetup<o></o>
.PrintTitleRows = "$2:$2"<o></o>
.PrintTitleColumns = ""<o></o>
End With<o></o>
Application.PrintCommunication = True<o></o>
Application.PrintCommunication = False<o></o>
With ActiveSheet.PageSetup<o></o>
.PaperSize = xlPaperA3<o></o>
End With<o></o>
Application.PrintCommunication = True<o></o>
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _<o></o>
IgnorePrintAreas:=False<o></o>
<o></o>
ActiveSheet.PageSetup.PrintArea = ""<o></o>
<o></o>
Unload Me<o></o>
End Sub<o></o>
<o>
''''''''''''''''''''Set Print Area and print to A4'''''''''''''''''
Private Sub PrintAllA4_Click()<o></o>
Dim LastRow As Long<o></o>
Dim LastCol As Long<o></o>
<o></o>
With ActiveSheet<o></o>
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row<o></o>
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column<o></o>
End With<o></o>
<o></o>
Range(Cells(1, 1), Cells(LastRow, LastCol)).Select<o></o>
ActiveSheet.PageSetup.PrintArea = Selection<o></o>
<o></o>
With ActiveSheet.PageSetup<o></o>
.PrintTitleRows = "$2:$2"<o></o>
.PrintTitleColumns = ""<o></o>
End With<o></o>
Application.PrintCommunication = True<o></o>
Application.PrintCommunication = False<o></o>
With ActiveSheet.PageSetup<o></o>
.PaperSize = xlPaperA4<o></o>
End With<o></o>



<o></o>
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _<o></o>
IgnorePrintAreas:=False<o></o>
ActiveSheet.PageSetup.PrintArea = ""<o></o>
Unload Me<o></o>
End Sub<o></o>

</o>


Regards

 

[Skagga5]

Anyone???

 

[Skagga5]

I think i might have found the solution to my problem - Application.PrintCommunication = True - should be added in front of the final print command.

The code should look like this -

Application.PrintCommunication = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _<O:P></O:P>
IgnorePrintAreas:=False
ActiveSheet.PageSetup.PrintArea = ""