VBA PDF Export printing 1 page

stevenbehr

New Member
Joined
Oct 15, 2019
Messages
15
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,092
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
15
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
15
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,092
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,082,305
Messages
5,364,406
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top