Excel VBA Page Setup changing

Hi

I call this code about 50 times to produce PDF docs that I need in portrait (one page wide)
but after I run the code and look at page setup it is back at landscape.
How do I force it to stay as portrait?

Sub oBuildReport(ByVal oLastRow As Integer, ByVal oClient As String, ByVal oJob As String)
   
    Dim BackupDate_Column As Integer: BackupDate_Column = 4
    Dim BackupStatus_Column As Integer: BackupStatus_Column = 5
   
    Dim WS As Worksheet
    Set WS = Sheets("PDF")
    WS.Cells.Delete Shift:=xlUp
   
    WS.Cells(24, 2).Value = "Backup Date"
    WS.Cells(24, 3).Value = "Backup Status"
   
    On Error Resume Next
    Dim arrBackupDate(9999) As String
    Dim arrBackupStatus(9999) As String
    Dim oIndexCount As Integer
    Dim oSuccessCount As Integer
    Dim oFailCount As Integer
   
    Dim oSuccessPerc As Integer
    Dim oFailPerc As Integer

    Dim oAddress As String
    Dim oRow As Integer
    Dim oVisibleRangeInColumn As String
    oVisibleRangeInColumn = "A2:A" & CStr(oLastRow)
   
    Dim rng As Range
    Set rng = Range(oVisibleRangeInColumn).SpecialCells(xlCellTypeVisible)

    Dim oCounter As Integer: oCounter = 24
   
    If Not rng Is Nothing Then
   
        oIndexCount = -1
        Dim cell As Range
        For Each cell In rng
 
            oCounter = oCounter + 1
            oIndexCount = oIndexCount + 1
            oAddress = cell.Address
            oRow = cell.Row
            WS.Cells(oCounter, 2).Formula = Cells(oRow, BackupDate_Column).Value
            WS.Cells(oCounter, 3).Formula = Cells(oRow, BackupStatus_Column).Value
           
            If arrBackupStatus(oIndexCount) = "Successful" Then
               oSuccessCount = oSuccessCount + 1
            Else
               oFailCount = oFailCount + 1
            End If
       
        Next cell
       
        Dim oTotalCount As Integer: oTotalCount = oSuccessCount + oFailCount
        Dim oPercentSuccess As Single: oPercentSuccess = oSuccessCount / oTotalCount * 100
        Dim oPercentFail As Single: oPercentFail = 100 - oPercentSuccess
       
   
       
        'oSuccessPerc = 80
        'oFailPerc = 20
        WS.Select
       
        WS.Range("B15") = "Success"
        WS.Range("B16") = oPercentSuccess
        WS.Range("C15") = "Fail"
        WS.Range("C16") = oPercentFail
       
        WS.Range("B15:C16").Select
        WS.Shapes.AddChart(xl3DPie, WS.Range("B8:M21").Left, WS.Range("B8:M21").Top, WS.Range("B8:M21").Width, WS.Range("B8:M21").Height).Select
        ActiveChart.SetSourceData Source:=Range("'" & WS.Name & "'!$B$15:$C$16")
        'ActiveChart.ChartType = xlPie
     
       
        'Dim rng2 As Range
        'Set rng2 = WS.Range("B8:M21")
    'ActiveChart.ChartArea.Left = rng2.Left
        'ActiveChart.ChartArea.Width = rng2.Width
        'ActiveChart.ChartArea.Top = rng2.Top
        'ActiveChart.ChartArea.Height = rng2.Height
       
          WS.Columns("B:B").EntireColumn.AutoFit
         WS.Columns("C:C").EntireColumn.AutoFit
       
        Dim oMonth As String: oMonth = Format(Date, "mmm")
        Dim oFileName As String: oFileName = oClient & "_Backup Report_" & oMonth
        Dim oFilePathAndName As String: oFileName = "C:\Users\murbro\Desktop\NetsurIT Test\" & oFileName & ".pdf"
       
 
         
         With WS.PageSetup
             .PrintArea = WS.UsedRange
             .Orientation = xlPortrait
             .FitToPagesWide = 1
             .Zoom = False 'I have added this line
         End With
       
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        oFileName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
       
        Sheet1.Select
           
   
    End If
End Sub
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Change this line of yours..

    With WS.PageSetup
             .PrintArea = WS.UsedRange
             .Orientation = xlPortrait
             .FitToPagesWide = 1
             .Zoom = False 'I have added this line
         End With
       
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        oFileName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
       
        Sheet1.Select

Open in new window



To this..

    With ws.PageSetup
             .PrintArea = ws.UsedRange
             .Orientation = xlPortrait
             .FitToPagesWide = 1
             .Zoom = False 'I have added this line
         End With
       
            ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        oFileName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
       
        Sheet1.Select

Open in new window

0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi Thank you, but as far as I can see you wanted me to change "WS" to "ws". This unfortunately didn't make a difference
0
Saurabh Singh TeotiaCommented:
No if you notice you are exporting activesheet..I'm not sure whether that is ws since ws is the one where you setting the page orientation to portrait..so now when i'm moving it to pdf if you notice im moving that then activesheet..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.