Export to PDF page sizing

[dantray02]

I have the following code to export a pdf of four worksheets in a workbook:

Sub exportfile()

Dim myArray
    myArray = Array("Cover Sheets", "profit and loss", "balance sheet", "wet and dry GP breakdown")
    
    Sheets(myArray).Select
    Selection.Activate

myPath = ThisWorkbook.Path & "\"
sFileName = myPath & Range("client").Value & " - Management accounts - " & _
    Format(Range("manp").Value, "dd Mmm yyyy") 'Without .pdf
'Check if file exists

If Dir(sFileName & ".pdf") <> "" Then
    sFileName = sFileName & Format(Now(), "_hh-mm")
End If
On Error Resume Next
sFileName = Left(sFileName, 214)
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=sFileName & ".pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

Sheets("cover sheets").Select
End Sub

the problem I am having is that some of the pages on the pdf are not A4, they are ridiculously large in comparison to the others. Although, when I select the tabs manually and print to PDF, everything is fine.

Any ideas?

thanks,
dan.

[Pedro Rodrigues]

Hi;

I use this code for doing the same, and it works nice;

What it does is to follow the document printing configurations;

I think this will do the trick;

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=chemin, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


My macro looks like this:

Sub PDF()


Dim sNomFichierPDF As String

code4G = ActiveSheet.Range("C9").Value 'first part of filename
datefn6 = Format(Date, "YYYYMMDD")

'If Len(datefn6) = 9 Then datefn6 = "0" & datefn6
Application.DisplayAlerts = False
Application.ScreenUpdating = False

chemin = ActiveWorkbook.Path & "\" & code4G & "_xxxx_" & datefn6
ThisWorkbook.Sheets(Array("sheet1", "sheet2", "sheet3", "sheet4")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=chemin, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

ActiveWorkbook.SaveAs chemin, FileFormat:=56
Application.DisplayAlerts = True 'IT WORKS TO DISABLE ALERT PROMPT
Application.ScreenUpdating = True

End Sub


Hope this is of any use to you...