Worksheet in Excel to output range objects data as PDF file, i want each range to be saved as separate pdf page?

Question

I would like to create PDF from multiple ranges on different pages.

For example:

1st page of PDF has first 5 columns of active sheet and columns from 6-10. So, range A1:E10 && F1:J10

2nd page of PDF has first 5 columns of active sheet and columns 10-15. So, range from A1:E10 && K1:O10

3rd page has range from A1:E10 && P1:T10

And so on.. code

Private Sub cmdPrintJul_Click()
  Dim ws1 As Worksheet, ws2 As Worksheet

  Set ws1 = Worksheets("YTD")
  ws1.PageSetup.PrintArea = "A1:K48"
  Set ws2 = Worksheets("July")
  ws2.PageSetup.PrintArea = "A1:G45"

  Worksheets(Array(ws1.Name, ws2.Name)).Select
  ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="e:\saved\July2016.pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

  ws1.Activate
  Range("A1").Select
End Sub

Does that accross multiple sheets, I need it on a single sheet, but concatenate multiple ranges together. Setting pages is probably gonna be done via PageSize, selecting the one that page breaks each combined range to its own page.

I tried with:

Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Celo leto 2019")
ws1.PageSetup.printArea = "B2:K39"
Set ws2 = Worksheets("Celo leto 2019")
ws2.PageSetup.printArea = "AQ2:BS39"

Worksheets(Array(ws1.Name, ws2.Name)).Select
ActiveSheet.ExportAsFixedFormat _
  Type:=xlTypePDF, _
  Filename:=strPathFile, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, _
  OpenAfterPublish:=True

ws1.Activate
Range("A1").Select

But it only exports ws2 range..

rob l rob l · Accepted Answer · 2021-06-07T13:34:29

in sample two, it appears you are trying to print title columns from the same worksheet. I assume you want columns b thru k to repeat on each page. (sample one appears to be two worksheets) try adding column titles

Dim ws1 As Worksheet

Set ws1 = Worksheets("Celo leto 2019")
With ws1
.PageSetup.PrintTitleColumns = "$b:$k"
.PageSetup.PrintArea = "$aq2:$bs39"
.ExportAsFixedFormat _
  Type:=xlTypePDF, _
  Filename:=strPathFile, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, _
  OpenAfterPublish:=True

End With

ws1.Activate
Range("A1").Select

Worksheet in Excel to output range objects data as PDF file, i want each range to be saved as separate pdf page?

1 Answers