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
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


Dim LastRow As Long<o


Dim LastCol As Long<o


<o


With ActiveSheet<o


LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row<o


LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column<o


End With<o


<o


Range(Cells(1, 1), Cells(LastRow, LastCol)).Select<o


ActiveSheet.PageSetup.PrintArea = Selection<o


<o


Application.PrintCommunication = False<o


With ActiveSheet.PageSetup<o


.PrintTitleRows = "$2:$2"<o


.PrintTitleColumns = ""<o


End With<o


Application.PrintCommunication = True<o


Application.PrintCommunication = False<o


With ActiveSheet.PageSetup<o


.PaperSize = xlPaperA3<o


End With<o


Application.PrintCommunication = True<o


ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _<o


IgnorePrintAreas:=False<o


<o


ActiveSheet.PageSetup.PrintArea = ""<o


<o


Unload Me<o


End Sub<o


<o

''''''''''''''''''''Set Print Area and print to A4'''''''''''''''''
Private Sub PrintAllA4_Click()<o


Dim LastRow As Long<o


Dim LastCol As Long<o


<o


With ActiveSheet<o


LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row<o


LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column<o


End With<o


<o


Range(Cells(1, 1), Cells(LastRow, LastCol)).Select<o


ActiveSheet.PageSetup.PrintArea = Selection<o


<o


With ActiveSheet.PageSetup<o


.PrintTitleRows = "$2:$2"<o


.PrintTitleColumns = ""<o


End With<o


Application.PrintCommunication = True<o


Application.PrintCommunication = False<o


With ActiveSheet.PageSetup<o


.PaperSize = xlPaperA4<o


End With<o


<o


ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _<o


IgnorePrintAreas:=False<o


ActiveSheet.PageSetup.PrintArea = ""<o


Unload Me<o


End Sub<o


</o

Regards
Last edited: