Format PDFs from excel VBA

[mickeystanford_alumni]

Hi guys, I got this VBA code to export a range of cells to a pdf directly. However, I am facing a few issues as:
1. My range is too big (wide in this case) that it makes it to be splitted in my pdf in 2 pages.
2. I wanted to export as well some figures, line graphs that are linked actually to the range that I selected. However, as it is a graph, the code doesn't recognize it.
3. If instead to export it to a pdf directly, I would like to put it in an outlook email, so then I can modify things in there/make some comments, how could I do it?

Any help on how to solve these?

Thanks in advance.

Public Sub FormatPDF()

Dim PDFranges As Variant, PDFrange As Variant
Dim PDFsheet As Worksheet
Dim destCell As Range
Dim copyRange As Range

PDFranges = Array("Sheet1!K37:AE46")

With ActiveWorkbook
Set PDFsheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
End With
Set destCell = PDFsheet.Range("A1")

For Each PDFrange In PDFranges
Set copyRange = ActiveWorkbook.Worksheets(Split(PDFrange, "!")(0)).Range(Split(PDFrange, "!")(1))

copyRange.Copy
destCell.Select
destCell.Worksheet.Paste
destCell.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

With PDFsheet
.HPageBreaks.Add Before:=.Rows(.UsedRange.Rows.Count + 1)
Set destCell = .Cells(.UsedRange.Rows.Count + 1, 1)
End With
Next

Application.CutCopyMode = False

PDFsheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="temp1.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

Application.DisplayAlerts = False
PDFsheet.Delete
Application.DisplayAlerts = True

End Sub

 

[sijpie]

To me it would be far easier to create a sheet to be printed (to pdf), with the ranges and graphs in the correct positions. Then print as pdf, where you can ensure that everything falls on one page.

 

[mickeystanford_alumni]

Hi Sijpie, however, by doing so I will have to manually create a sheet every time I want to print right?
I wanted to automate this so I can weekly print as pdf and make that quick.

I might be misunderstanding what you mean tho.

 

[sijpie]

I would write some VBA macro to copy the relevant ranges and graphs to a temporary sheet. Then print & delete sheet . That can all be automated. You can even set up a template sheet with additional text etc to be used for this.

 

[mickeystanford_alumni]

Thank you Sijpie, makes sense.