0

I'm trying out some very simple code in Excel. 

Sub PDF()
Path = "C:\users\MyName\Desktop\Name"
MsgBox (Path)

ThisWorkbook.Sheets(1).ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=Path, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

'
End Sub

This code works just fine, it exports the first tab as an excel document. However, what I want is for it to be able to export multiple sheets as a single PDF. So I tried this thinking it would work: 

ThisWorkbook.Sheets(1,2,3).ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=Path, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

And that didn't work. I also tried: 

ThisWorkbook.Sheets(array(1,2,3,4)).ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=Path, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

I've seen a few suggestions that suggest using 

Array("Sheet1", "Sheet2")

However I do not want to use the sheet names and instead I want to use the sheet's index. I'm sure there is a blatantly obvious solution but I've tried searching and can't find it.

Jed Bartlet
  • 1,963
  • 2
  • 11
  • 12
  • there sure is! use a loop! https://excelmacromastery.com/vba-for-loop/#Example_2_Printing_1_to_20 – Marcucciboy2 Sep 13 '18 at 20:22
  • Hmmm, sorry I'm a bit unclear how the loop helps here? I don't want to create 3 different PDFs of 1 tab each, I want to combine 3 tabs into 1 pdf. I am familiar with for loops etc., not sure how it applies here though? – Jed Bartlet Sep 13 '18 at 20:29
  • 1
    See here: https://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf – Frank Ball Sep 13 '18 at 20:34
  • @FrankBall I looked at that question, and that's where I saw that you could do Array("Sheet 1", "Sheet 2"....) but as I indicated in my question I don't want to do it by sheet name. Is there a way to do it without using the sheet name and only uisng the index? – Jed Bartlet Sep 13 '18 at 20:44
  • @FrankBall Sorry it just clicked, I have to select it, I was trying to use the same method but without selecting it. It works now. Thank you! – Jed Bartlet Sep 13 '18 at 20:48

0 Answers0