VBA PDF Export printing 1 page

[Legacy 452653]

I have used this VBA code for publishing a PDF report which does work, but what it is doing is showing 2 blank pages below the PDF report been published.

Is there anyway to say to the report to export just the range I want leaving just 1 page instead of throwing out good paper!.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub PrintPDFTrainingReport_Click()
'turn off screen updating
Dim Opendialog
Dim MyRange As Range
Application.ScreenUpdating = False
'open dialog and set file type
Opendialog = Application.GetSaveAsFilename("", filefilter:="PDF Files (*.pdf), *.pdf", _
Title:="Training Due Report")
'if no value is added for file name
If Opendialog = False Then
MsgBox "The operation was not successful"
Exit Sub
End If
'set the named range for the PDF print area
Sheet2.Select
With Sheet2
.Range("T1:AC1" & Cells(Rows.Count, "T").End(xlUp).Row).Name = "PDFRng"
End With
'set range
Set MyRange = Sheet2.Range("PDFRng")
Sheet2.PageSetup.Orientation = xlLandscape
'Sheet2.PageSetup.FitToPagesWide = 1
Sheet2.PageSetup.Zoom = 75
Sheet2.PageSetup.PrintArea = "PDFRng"
'create the PDF
On Error Resume Next
MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Opendialog, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'error handler
On Error GoTo 0
'clear the page breaks
ActiveSheet.DisplayPageBreaks = False
Application.ScreenUpdating = False
Sheet1.Select
End Sub

Thanks

Steve[/FONT]_{^{<strike>
</strike>}}[/FONT]

 

[Kenneth Hobson]

Sheet2.PageSetup.FitToPagesTall = 1

I suspect that your real or additional problem is:

.Range("T1:AC[COLOR="#FF0000"]1[/COLOR]" & Cells(Rows.Count, "T").End(xlUp).Row).Name = "PDFRng"

 

[Legacy 452653]

Arrr Yes Ken,

You are right was meant to do a Current_Region in my data and Delete PDFrng then Put "Current_Region". That's why it was taking the blank screen to the bottom!

Thanks for the help

Steve

 

[Legacy 452653]

Well There goes that idea Ken,

I input the following line and it still creates 3 pages. Below is the code and I left the comas in to make it a text.

Sheet2.Select
Sheet2.PageSetup.FitToPagesTall = 1
With Sheet2
'.Range("T1:AC1" & Cells(Rows.Count,"T").End(xlUp).Row).Name = "Current_Region"
'End With
'set range
'Set MyRange = Sheet2.Range("Current_Region")
'Sheet2.PageSetup.Orientation = xlLandscape
'Sheet2.PageSetup.FitToPagesWide = 1
'Sheet2.PageSetup.Zoom = 75
'Sheet2.PageSetup.PrintArea = "Current_Region"
'create the PDF
On Error Resume Next
MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Opendialog,Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
'error handler


Alls I want is just to print a current region area and export it too PDF

Let me know if I missed some thing

Steve

 

[Kenneth Hobson]

The normal method is to set the print area and then publish the worksheet to pdf but you can use a range to override the print area.

You commented out the width setting.

You can set the print area first and then select View and Page Break Preview to see what is going on. Change one setting and then record a macro and set it back so that Page Break Preview shows the one page if you need code.

Select, Activate, and Selection are seldom needed.

Sub Main()
  Dim MyRange As Range
  With Sheet2
    Set MyRange = .Range("T1:AC" & .Cells(Rows.Count, "T").End(xlUp).Row)
    .PageSetup.Orientation = xlLandscape
    .PageSetup.FitToPagesWide = 1
    .PageSetup.Zoom = 75
    '.PageSetup.PrintArea = "Current_Region"
    'create the PDF
    MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Opendialog, Quality:=xlQualityStandard, _
     IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
  End With
End Sub