Change page orientation of all visible sheets

[wtom0412]

Hi All,

I have tried many things to get this to work, but so far, I have drawn a blank!! I need to be able to print to PDF in landscape. Here is the code to print multiple (visible) sheets to a single PDF (it works perfectly). There are about 20 visible sheets remaining.

Sub PrintPDF()
Dim sMsg As String, FName As Variant
Dim myArray() As Integer
Dim i As Integer
Dim j As Integer


Application.ScreenUpdating = True


Sheet1.Visible = False
Sheet2.Visible = False
Sheets("Sheet Menu").Visible = False
Sheet39.Visible = False
Sheet70.Visible = False


j = 0
For i = 1 To Sheets.Count
    If Sheets(i).Visible = True Then
        ReDim Preserve myArray(j)
        myArray(j) = i
        j = j + 1
    End If
Next i


FName = Application.GetSaveAsFilename(InitialFileName:="Insert File Name Here.pdf", FileFilter:="PDF files, *.pdf", Title:="Export to pdf")
If FName <> False Then
    Sheets(myArray).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End If
ActiveSheet.Select


Sheet1.Visible = True
Sheet2.Visible = True
Sheets("Sheet Menu").Visible = True
Sheet39.Visible = True
Sheet70.Visible = True


Sheets("Enter Products").Select


Application.ScreenUpdating = False


End Sub

I have put the following code in ThisWorkbook Objects, but it will only print the first page in Landscape, the rest are still in Portrait.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

ActiveSheet.PageSetup.Orientation = xlLandscape

End Sub

I understand why - it's because the code says "ActiveSheet". I have tried changing it to ThisWorkBook.PageSetup.Orientation = xlLandscape, but it gives me "Compile Error: Method or data member not found".

Is there something I can add to the main code, or fix the BeforePrint option?

Any help would be greatly appreciated.

Cheers, WT

 

[Fluff]

Not sure if this will work, but try

For i = 1 To Sheets.Count
    If Sheets(i).Visible = True Then
        [COLOR=#ff0000]Sheets(i).PageSetup.Orientation = xlLandscape[/COLOR]
        ReDim Preserve myarray(j)
        myarray(j) = i
        j = j + 1
    End If
Next i

 

[wtom0412]

Perfectly, thank you so much Fluff. I have been wrestling with this for about three hours!!

 

[Fluff]

You're welcome & thanks for the feedback