Combining multiple sheets to PDF - missing all but first sheet on output

[raulsanchez]

Hi Everybody,

Today I stumbled upon a quirky issue. Manually I have been saving 2 sheets as PDF, and they have been combining into one PDF file very nicely. This includes the "page 1 of x" sequencing in the footers. Because this is becoming repetative for some workbooks, I wrote a macro to do it for me. However, when the macro does the work, it exports only the first of the two selected sheets. My output PDF is missing the second page. This is the code that I am using:

Sheets(Array("NonCorporation Report", "Other Expenses List")).Select
Sheets("NonCorporation Report").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\.psf\Home\Desktop\testname.pdf", _
       Quality:=xlQualityStandard, _
       IncludeDocProperties:=True, _
       IgnorePrintAreas:=False, _
       OpenAfterPublish:=False

For the purpose of comparing with the macro recorder, I recorded myself saving 2 sheets as a PDF. It gave me identical code from the recorder, but worked. And this is what is confusing, when I use the Excel user interface to save multiple sheets as a single PDF, it works perfectly. When I run the code from the VBE, it drops the second sheet from the process regardless of it being selected in the array.

I am familiar with Ron de Bruin's work that he has shared on his website, but didn't find any clues as to what could be causing this issue. If anybody has any insight, please share. Thanks everybody!

-Raul

 

[raulsanchez]

UPDATE: This code is now working. Restarting Excel seems to have "fixed" it. I am still curious if anybody else has encountered this problem before, and if so, what did they do to handle it. My workbooks will be distributed to clients and staff with a nice easy command button to allow them to quickly export to PDF, but it will be very troublesome if they run into the same troubles I had today. Restarting Excel every time it doesn't respond correctly isn't a solution in my opinion. Thoughts?