VBA PDF Export printing 1 page

L

Legacy 452653

Guest
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]
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top