VBA PDF Export printing 1 page

stevenbehr

New Member
Joined
Oct 15, 2019
Messages
32
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
32
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
32
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,084,738
Messages
5,379,524
Members
401,611
Latest member
CandaceR68

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top