VBA to generate one pdf report with multiple ranges

[Kov4n]

Hi,
I am trying to create a VBA code which will generate a pdf report where multiple ranges from a sheet will be printed onto one page.
In order to achieve this the ranges have to follow after each, hope that make sense....

So far this is the code I have been able to write


Private Sub CommandButtonPrintReport1_Click()

Sheet11.Range("G1:T5,A6:O34,P6:AC34").ExportAsFixedFormat xlTypePDF, Environ("Userprofile") & "\OneDrive\Documents\Excel Templates\Management Reports\SCL_Report1.pdf"

End Sub


The ranges are charts and tables and I need them to printed one range under the other as a portrait pdf when a command button is pressed.

Currently one pdf is created but each range is printed on a separate sheet

Hoping someone can help....

 

[MCLIFTO8]

Can you copy those ranges over to a hidden intermediary sheet and then define the pdf export as one range?

 

[Kov4n]

Can you copy those ranges over to a hidden intermediary sheet and then define the pdf export as one range?

This is something new to me and will have to look further into this…thank you for advising as this request I am finding hard to get solutions for. Do you have any notes or links I can learn more about hidden intermediary sheets?

 

[MCLIFTO8]

So that's not a keyword. I'm referring to literally creating a blank "helper" sheet then hiding it form the user with VBA and using that hidden sheet to copy-paste your ranges across to build the layout of your PDF, then sleecting the whole thing as one range so the export method formats it as one page.

Creating a new sheet
optional, you could do this manually

Add Sheet

Sheets.Add

Get ActiveSheet

MsgBox ActiveSheet.Name

Hiding sheets
Sheets("Sheet1").visible = False
OR
Sheets("Sheet1").visible = xlSheetHidden

copypasting ranges

Copy range directly (fastest)	vRangeToCopy = ws1.Range("A1:A3") ws2.Range("B1:B3").Value2 = vRangeToCopy
Copy range (without clipboard, faster)	Set rng = Worksheets("Sheet1").Range("A1:Z100") Worksheets("Sheet2").Range("A1").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value
Copy range using clipboard	Range.Copy method (Excel) Range(“A1:B3”).Copy Range(“D1”) .Range("F5").Copy Destination:=.Range("A1:A5")
Paste special	Range.PasteSpecial method (Excel) Range(“A1:B3”).Copy Range("B1").PasteSpecial Paste:=xlPasteValues Paste:=xlPasteFormats

export as pdf
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\users\default\Desktop\myWorkbook.pdf"

Program flow
I would start with experimenting around creating a new sheet, it will always be the activeSheet because it was just made.
Copy pasting the ranges to the active sheet.
Exporting the activesheet over to PDF.
Deleting the active active sheet.

 

[John_w]

Hi,
I am trying to create a VBA code which will generate a pdf report where multiple ranges from a sheet will be printed onto one page.
In order to achieve this the ranges have to follow after each, hope that make sense....

So far this is the code I have been able to write


Private Sub CommandButtonPrintReport1_Click()

Sheet11.Range("G1:T5,A6:O34,P6:AC34").ExportAsFixedFormat xlTypePDF, Environ("Userprofile") & "\OneDrive\Documents\Excel Templates\Management Reports\SCL_Report1.pdf"

End Sub


The ranges are charts and tables and I need them to printed one range under the other as a portrait pdf when a command button is pressed.

Currently one pdf is created but each range is printed on a separate sheet

Hoping someone can help....

Try this macro.

Public Sub Save_Ranges_As_Contiguous_PDF()

    Dim PDFranges As Range, PDFrange As Range
    Dim tempSheet As Worksheet
    Dim PDFfile As String
    Dim destCell As Range
    
    With ActiveWorkbook
        Set PDFranges = .ActiveSheet.Range("G1:T5,A6:O34,P6:AC34")
        Set tempSheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
        PDFfile = Environ("Userprofile") & "\OneDrive\Documents\Excel Templates\Management Reports\SCL_Report1.pdf"
    End With
    
    tempSheet.PageSetup.Orientation = xlPortrait
    Set destCell = tempSheet.Range("A1")
        
    For Each PDFrange In PDFranges.Areas
        PDFrange.Copy destCell
        Set destCell = destCell.Offset(PDFrange.Rows.Count + 1)
    Next
    
    tempSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
    
    Application.DisplayAlerts = False
    tempSheet.Delete
    Application.DisplayAlerts = True
    
    MsgBox "Created " & PDFfile, vbInformation

End Sub

 

[Kov4n]

Try this macro.

Public Sub Save_Ranges_As_Contiguous_PDF()

    Dim PDFranges As Range, PDFrange As Range
    Dim tempSheet As Worksheet
    Dim PDFfile As String
    Dim destCell As Range
   
    With ActiveWorkbook
        Set PDFranges = .ActiveSheet.Range("G1:T5,A6:O34,P6:AC34")
        Set tempSheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
        PDFfile = Environ("Userprofile") & "\OneDrive\Documents\Excel Templates\Management Reports\SCL_Report1.pdf"
    End With
   
    tempSheet.PageSetup.Orientation = xlPortrait
    Set destCell = tempSheet.Range("A1")
       
    For Each PDFrange In PDFranges.Areas
        PDFrange.Copy destCell
        Set destCell = destCell.Offset(PDFrange.Rows.Count + 1)
    Next
   
    tempSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
   
    Application.DisplayAlerts = False
    tempSheet.Delete
    Application.DisplayAlerts = True
   
    MsgBox "Created " & PDFfile, vbInformation

End Sub

Thank you for the reply but this still prints over two pages and also misses some cells when printing into a PDF.

 

[John_w]

The code specifies exactly the same ranges as your OP, so I'm not sure why it's missing some cells.

Insert this code before the tempSheet.ExportAsFixedFormat to reduce the top and bottom margins to zero.

    With tempSheet.PageSetup
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
    End With

Maybe you also need to reduce the left and right margins and/or column widths and row heights to make everything fit onto 1 page - record a macro and see if you can integrate it into my code.

 

[Kov4n]

The code specifies exactly the same ranges as your OP, so I'm not sure why it's missing some cells.

Insert this code before the tempSheet.ExportAsFixedFormat to reduce the top and bottom margins to zero.

    With tempSheet.PageSetup
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
    End With

Maybe you also need to reduce the left and right margins and/or column widths and row heights to make everything fit onto 1 page - record a macro and see if you can integrate it into my code.

Thank you John, will have a little play around and see if I can use your code to make it work, will update you as soon as I get a chance to try making it work