Save the data as PDF

[ajaar]

Dear Experts,
I am stuck with something, appreciate any help.
I have below code to copy the data from a report, paste in a invoice template and print out. Page number will show in each page example-1/3,2/3,3/3 etc.
I wanted to run the same code and save as pdf instead of printout.
pdf will be multiple page if exceeding one page- page number stored in variable iMaxPage.

any help would be highly appreciated.

Thanks
Ajaar


]

Sub pdfpdf()

'Call update

Const iCopyRows = 31
Dim i As Variant, copyrng As Range, iRows As Integer, iMaxPage As Integer, DataSheet As Worksheet, L As Integer
Dim UList As Collection, UListValue As Variant

Sheet8.Range("U2:AR2000").ClearContents

Set DataSheet = Sheets("IPD")
Set UList = New Collection
With Sheets("IPD-Invoice")

On Error Resume Next
For i = 3 To DataSheet.[A2].CurrentRegion.Rows.Count
UList.Add DataSheet.Cells(i, 19), CStr(DataSheet.Cells(i, 19))
Debug.Print DataSheet.Cells(i, 19)
Next i
On Error GoTo 0

.Range("C19:V51").ClearContents

For Each UListValue In UList
DataSheet.[U2] = "InvoiceNo"
DataSheet.[U3] = UListValue
DataSheet.Range("A2").CurrentRegion.AdvancedFilter Action:=2, CriteriaRange:=DataSheet.[u2:u3], CopyToRange:=DataSheet.Range("V2"), Unique:=False

iRows = DataSheet.[V2].CurrentRegion.Rows.Count
iMaxPage = Application.Ceiling((iRows - 1) / iCopyRows, 1)

L = 0
For i = 3 To iRows Step iCopyRows
L = L + 1

Set copyrng = DataSheet.Range("V" & i & ":AN" & i + iCopyRows - 1)
copyrng.Copy
.Range("C19").PasteSpecial Paste:=xlPasteValues

.[H1].Value = "Page " & L & " of " & iMaxPage
.[M16].Value = L

If iMaxPage = L Then
'.[G50].Value = Application.CountA(DataSheet.Columns("AD"))
'.[I50].Value = Application.Sum(DataSheet.Columns("AD"))
Else
'.[G50].Value = ""
' .[I50].Value = ""
End If
.printout 1, 1, 1
Next i
DataSheet.Range("V1").CurrentRegion.ClearContents
Next UListValue
.Range("A19:v51").ClearContents
Set DataSheet = Nothing
Set UList = Nothing
End With

MsgBox "Done"

End Sub[

 

[6StringJazzer]

Replace .printout with .ExportAsFixedFormat. Specification here.