0

I am having an issue with saving multiple worksheets as a pdf. I have looked on this website (and others) and found similar code to what I have been using. However, the code that I have modified to more closely resemble Save multiple sheets to .pdf and http://www.vbaexpress.com/kb/getarticle.php?kb_id=160 combined.

However, I am running into an error in my last line "424: Object Required". If you could steer me in the right direction it would be greatly appreciated!

Sub SaveAs()

Dim Fname As String
Dim Fpath As String

    Fname = Sheets("Sheet1").Range("FT5").Text 'The Cell I want to use as my file name
    Fpath = "C:" 'my location

    ThisWorkbook.Sheets(Array("Sheet 1", "Sheet 2")).Select 'My selected tabs

    ActiveSheets.ExportAsFixedFormat Type:=xlTypePDF, _
        FileName:=Fpath & "\" & Fname & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True 'My issue is with this line....
End Sub
Community
  • 1
  • 1
user2437803
  • 77
  • 3
  • 3
  • 7

1 Answers1

1

There is no such object as ActiveSheets, therefore your last line does cause an error.

Replace ActiveSheets either with ActiveWindow.SelectedSheets if you want to simply print what the user currently has selected or with Sheets("Sheet 1", "Sheet 3", "Sheet ...") if you want to selected the sheets with the code.

Peter Albert
  • 16,917
  • 5
  • 64
  • 88
  • I selected yours as the correct answer; however, simply removing the 's' from the end of ActiveSheets was all that I needed to do. Thank you for helping me out Peter! – user2437803 Jun 06 '13 at 20:44
  • Good to know! Actually, looking at your code again, I'd recommend to simply merge the two last lines to `ThisWorkbook.Sheets(Array("Sheet 1", "Sheet 2")).ExportAsFixedFormat(...` – Peter Albert Jun 06 '13 at 20:58
  • Thanks, for letting me know I can do that! I think I am going to leave it the way it is though. I want to be able to easily edit my array! – user2437803 Jun 06 '13 at 21:17
  • You still can edit your array - you just remove the intermediate step of actually selecting the worksheets! It's the same principle as to not use `Range.Select`, `Selection.DoSomething` - but rather `Range.DoSomething`. See here for further details: http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select – Peter Albert Jun 06 '13 at 21:53