VBA for exporting sheets to pdf

[Matkan1992]

Hi

I'm a real rookie in the VBA world, so I hope you will bear with me.

I've been working on a workbook, where the finishing touch is, that it generates a PDF with the final results. This means combining ranges from different sheets.

So far I've manage to create an VBA code which creates pdf's but it does a separate pdf for each sheet.

Can anyone help me adjust my code, so it saves only one combined pdf?

My code so far:

Sub ExporterPDF1()
Sheet1.Range("B1:K27").ExportAsFixedFormat xlTypePDF, Environ("UserProfile") & "\Desktop\TEST.PDF"

Sheet7.Range("A4:J37").ExportAsFixedFormat xlTypePDF, Environ("UserProfile") & "\Desktop\TEST1.PDF"


Sheet3.Range("A1:H24").ExportAsFixedFormat xlTypePDF, Environ("UserProfile") & "\Desktop\TEST2.PDF"
End Sub

 

[gallen]

I may be wrong but I think the only way of achieving this would be to paste the ranges to a new sheet with page breaks between each range then export the sheet (All in VBA of course)

You can't append to PDF and you can't export ranges from other sheets at the same time.

 

[gallen]

OK, I think I've managed it. (Welcome to the boards BTW)

This code creates a temp sheet, fills it with the ranges, adds pagebreaks, exports the sheet then deletes the sheet

Sub MakePDF()

Dim r As Range
Dim sFileName As String
Dim wsTemp As Worksheet
Dim nr As Long 'Next row to paste to

    Set wsTemp = ThisWorkbook.Worksheets.Add
    
    sFileName = "C:\Test\UnionTest" 'Change this path and name accoridngly
    
    nr = 1
    '***********First Range To Export***************
    Set r = Sheet1.Range("B1:K27")
    
    r.Copy wsTemp.Cells(nr, 1)
    
    nr = nr + r.Rows.Count
    
    wsTemp.HPageBreaks.Add Before:=wsTemp.Cells(nr, 1)
    '*************2nd Range To Export*******************
    
    Set r = Sheet7.Range("A4:J37")
    
    r.Copy wsTemp.Cells(nr, 1)
    
    nr = nr + r.Rows.Count
    
     wsTemp.HPageBreaks.Add Before:=wsTemp.Cells(nr, 1)
    '**************3rd Range To Export*****************
    
    Set r = Sheet3.Range("A1:H24")
    
    r.Copy wsTemp.Cells(nr, 1)
    
    nr = nr + r.Rows.Count
    
     wsTemp.HPageBreaks.Add Before:=wsTemp.Cells(nr, 1)
    '*************************************************

    With wsTemp.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    
    wsTemp.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileName
    Application.DisplayAlerts = False
    wsTemp.Delete
    Application.DisplayAlerts = True
    
End Sub