VBA PDF Export printing 1 page

stevenbehr

New Member
Joined
Oct 15, 2019
Messages
11
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

Well-known Member
Joined
Feb 6, 2007
Messages
3,082
Code:
Sheet2.PageSetup.FitToPagesTall = 1
I suspect that your real or additional problem is:
Code:
.Range("T1:AC[COLOR="#FF0000"]1[/COLOR]" & Cells(Rows.Count, "T").End(xlUp).Row).Name = "PDFRng"
 
Last edited:

stevenbehr

New Member
Joined
Oct 15, 2019
Messages
11
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
 

stevenbehr

New Member
Joined
Oct 15, 2019
Messages
11
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

Well-known Member
Joined
Feb 6, 2007
Messages
3,082
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.
Code:
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
 
Last edited:

Forum statistics

Threads
1,077,827
Messages
5,336,611
Members
399,092
Latest member
jbwatkins

Some videos you may like

This Week's Hot Topics

Top