excel vba not exporting pagesetup to pdf correctly

Question

I have code which formats a worksheet to the desired setup and layout (one page wide and tall in landscape). When I run the code (part of a long macro) it formats the pagesetup correctly.

If I manually export and save it as a pdf, then it uses the correct page setup, producing a one page PDF that is in landscape. However, the same export done by VBA produces a PDF that is severalpages long and in portrait.

i can't figure out why it's doing this. i've tried various solutions such as selecting the worksheet before exporting it, but all to no avail.

Any help is appreciated.

Code looks like this:

Sub SaveAsPDF()
Sheets(ReportWsName).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        [SaveFolderPath] & "\" & ReportWsName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
End Sub

UPDATE:

Code used to format the pagesetup (since it's rather long I am only adding the relevant section of that sub)

Private Sub CreateNewReport(ProvisionCode As String, TimeFrom As Date, TimeTo As Date)

... other code here...

'Format report to create the desired layout
With Worksheets(ReportWsName)
    'Delete unnecessary data and format the rest
    .Range("A:B,D:D,F:G,J:M,O:O,Q:S").Delete Shift:=xlToLeft
    .Range("A:F").EntireColumn.AutoFit
    .Range("C:C, E:F").ColumnWidth = 30
    With .Range("G:G")
        .ColumnWidth = 100
        .WrapText = True
    End With
    'Insert standard formating header form Reporting template
    .Rows("1:2").Insert
    wsReportTemplate.Range("1:3").Copy .Range("A1")
    .Range("A2") = "Notes Report for " & ProvisionCode & " (" & TimeFrom & " - " & TimeTo & ")"
    'Insert standard formating footer form Reporting template
    wsReportTemplate.Range("A6:G7").Copy .Range("A" & .UsedRange.Rows.Count + 2)
    'Ensure all data is hard coded
    .UsedRange.Value = .UsedRange.Value
    'Format Print Area to one Page
    With ActiveSheet.PageSetup
        .PrintArea = Worksheets(ReportWsName).UsedRange
        .Orientation = xlLandscape
        .FitToPagesWide = 1
    End With
End With

End Sub

Answer 1

I have found what seems to be the solution:

Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        '.PrintArea = Worksheets(ReportWsName).UsedRange
        .FitToPagesWide = 1
        '.FitToPagesTall = 1
    End With
Application.PrintCommunication = True

I needed to add the Application.PrintCommunication part to the equation. For whatever reason Excel would overwrite the settings I was putting if I ran the code without it.

Answer 2

I think the problem is that you need to add the .Zoom = False to your page setup code:

'Format Print Area to one Page
With ActiveSheet.PageSetup
    .PrintArea = Worksheets(ReportWsName).UsedRange
    .Orientation = xlLandscape
    .FitToPagesWide = 1
    .Zoom = False 'I have added this line
End With

From what I have tried this should solve it for you.

Let me know how it goes!

EDIT: Maybe you need:

'Format Print Area to one Page
With ActiveSheet.PageSetup
    .PrintArea = Worksheets(ReportWsName).UsedRange
    .Orientation = xlLandscape
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .Zoom = False 'I have added this line
End With

EDIT2: What if you changed:

.PrintArea = Worksheets(ReportWsName).UsedRange

To

.PrintArea = Worksheets(ReportWsName).UsedRange.Address

Answer 3

Yes!!!, I have had the same problem: I was not able to export a sheet with the page Setup settings already applied on it.

Before trying the Application.PrintCommunication I tested Wait and Sleep commands without success. Finally I skipped this issue by using CopyPicture method, adding a chart page and then exporting it to pdf, but resolution in my pdf it was not fine and I was not able to play with margins.

So just add Application.PrintCommunication=false before your code , on pagesetup settings like CaptainABC says and most important: close with Application.PrintCommunication=true after the code.

Thank you for this useful post.