Export PDF from ranges on two different worksheets

ldcycler · Nov 21st 2017

I have two worksheets, YTD and July. I want to select A1:K48 on the YTD worksheet and A1:G45 on the July worksheet and then export that selection to a pdf file on the e:\saved folder with the name July2017. I get an error on the Set rng line of code. Here is what I have done and appreciate any help on this. Thank you

Private Sub cmdPrintJul_Click()

Set rng = Worksheets("YTD").Range("A1:K48", Worksheets("July").Range("A1:G45"))
rng.Select

Selection.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="e:\saved\July2016.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Sheets("YTD").Range("A1").Select

End Sub

**Kenneth Hobson** · Nov 21st 2017

Welcome to the forum! Please paste code between code tags. Click A icon in top right of a reply box and then # icon in reply toolbar.

Selection can not span ranges on 2 sheets. The key is selecting sheets. ActiveSheet is the selected sheet(s). You will probably want to set some more PageSetup options.

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

Display More

Export PDF from ranges on two different worksheets

Participate now!

Share