Variable paper size for printing

[MadeleineB]

I'm trying to set the paper size of a worksheet by using a variable from a cell. (see code below) The variable for how many copies works but the paper size does not. If I use the following code (in green) instead on the code in red it works. HELP!

Worksheets("RAG Summary").PageSetup.PaperSize = xlPaperA3

------------------------------------------------------------------------------------------
Dim MyPaper As String
Dim MySize As String
Dim MyCopy As String

MyPaper = ThisWorkbook.Sheets("Publish").Range("N4")
MySize = "xlPaper" & MyPaper
MyCopy = ThisWorkbook.Sheets("Publish").Range("N6")

Worksheets("RAG Summary").PageSetup.PaperSize = MySize

Sheets(Array("RAG Summary", "Progress")).Select
Sheets("RAG Summary").Activate
'ActiveWindow.SelectedSheets.PrintPreview
ActiveWindow.SelectedSheets.PrintOut Copies:=MyCopy, Collate:=True

 

[VoG]

Hello and welcome to MrExcel.

I think that you will need to use the numerical equivalent of that constant in the code (either read from a cell or hardcoded as I have done):

MySize = 8
Worksheets("RAG Summary").PageSetup.PaperSize = MySize

 

[MadeleineB]

So could I do a IF statment; therefor if cell N4 = A3 it will be 8 and IF N4 = A4 it will be 9?

If yes please could you help as I don't know how to write one

 

[VoG]

Try (changes in red):

Dim MyPaper As String
Dim MySize As Integer
Dim MyCopy As String

MyPaper = ThisWorkbook.Sheets("Publish").Range("N4")
If MyPaper = "A4" Then
    MySize = 9
ElseIf MyPaper = "A3" Then
    MySize = 8
End If
MyCopy = ThisWorkbook.Sheets("Publish").Range("N6")

Worksheets("RAG Summary").PageSetup.PaperSize = MySize

Sheets(Array("RAG Summary", "Progress")).Select
Sheets("RAG Summary").Activate
'ActiveWindow.SelectedSheets.PrintPreview
ActiveWindow.SelectedSheets.PrintOut Copies:=MyCopy, Collate:=True

 

[MadeleineB]

Great that works! Thanks for much for your help.

Madeleine