Export Range as a PDF

shophoney

Board Regular
Joined
Jun 16, 2014
Messages
242
Hi,

Below is some code I'm working on. I want to export a dynamic range as a PDF. But the code below produces blank pages as the range varies from 5 lines to 100 lines.


Public Sub Class_Sales_PDF()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Dim lastE As Long, _
lastK As Long, _
lastP As Long, _
lastW As Long, _
lastAB As Long, _
lastAG As Long, _
lastAL As Long, _
lastAQ As Long, _
lastBB As Long, _
lastBI As Long, _
lastBQ As Long, _
lastBX As Long, _
lastCF As Long, _
lastCL As Long, _
lastCP As Long, _
lastCU As Long

Dim PDFranges As Range
On Error GoTo errHandler

With ActiveSheet
lastE = .Cells(.Rows.Count, "E").End(xlUp).Row
lastK = .Cells(.Rows.Count, "K").End(xlUp).Row
lastP = .Cells(.Rows.Count, "P").End(xlUp).Row
lastW = .Cells(.Rows.Count, "W").End(xlUp).Row
lastAB = .Cells(.Rows.Count, "AB").End(xlUp).Row
lastAG = .Cells(.Rows.Count, "AG").End(xlUp).Row
lastAL = .Cells(.Rows.Count, "AL").End(xlUp).Row
lastAQ = .Cells(.Rows.Count, "AQ").End(xlUp).Row
lastBB = .Cells(.Rows.Count, "BB").End(xlUp).Row
lastBI = .Cells(.Rows.Count, "BI").End(xlUp).Row
lastBQ = .Cells(.Rows.Count, "BQ").End(xlUp).Row
lastBX = .Cells(.Rows.Count, "BX").End(xlUp).Row
lastCF = .Cells(.Rows.Count, "CF").End(xlUp).Row
lastCL = .Cells(.Rows.Count, "CL").End(xlUp).Row
lastCP = .Cells(.Rows.Count, "CP").End(xlUp).Row
lastCU = .Cells(.Rows.Count, "CU").End(xlUp).Row

Set PDFranges = .Range("A2:E" & lastE & _
",G2:K" & lastK & _
",M2:P" & lastP & _
",R2:W" & lastW & _
",Y2:AB" & lastAB & _
",AD2:AG" & lastAG & _
",AI2:AL" & lastAL & _
",AN2:AQ" & lastAQ & _
",AX2:BB" & lastBB & _
",BD2:BI" & lastBI & _
",BK2:BQ" & lastBQ & _
",BS2:BX" & lastBX & _
",BZ2:CF" & lastCF & _
",CH2:CL" & lastCL & _
",CN2:CP" & lastCP & _
",CR2:CU" & lastCU)
End With

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

strPath = "D:\Dropbox\Shared_Files\Reports"
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

strFile = ActiveSheet.Range("A2").Value & " - " & strTime & ".pdf"
strPathFile = strPath & strFile

myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")

If myFile <> "False" Then
wsA.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& myFile
End If

exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,111
Suggestion:

Copy the dynamic ranges to a blank sheet. Then print that sheet as PDF.
 

Forum statistics

Threads
1,136,300
Messages
5,674,961
Members
419,536
Latest member
Mohammed Jaffer

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
Top