export multiple excel sheets into separate pdfs

[cycouten]

Hello, I am stuck with this macro which exports the first file properly and then closes the excel file and leaves the macro box open with all closed !!! I simply want to create separate pdfs for each sheet after FIRST and up to the one prior to LAST. Such routine worked well in exporting into xlsx files, but with pdf it does not. Any clue on this one would be appreciated as I am just a few days old working in VBA ... Tks.

_________________________________________________________________
Sub create_separate_pdf_between_FIRST_and_LAST()

Dim xPath As String
Dim I As Integer
Dim QTR As String
QTR = ThisWorkbook.Sheets("HELP").Range("Z6")
xPath = Application.ActiveWorkbook.Path

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For I = Sheets("FIRST").Index + 1 To Sheets("LAST").Index - 1
Sheets(I).Select
Sheets(I).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
xPath & "\" & QTR & Format(Now, "yyyymmdd hh-mm") & "_" & Sheets(I).Name & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Application.ActiveWorkbook.Close False
Next I

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[cycouten]

what do you mean

 &

? sorry, i cannot see these 2 tags in "vocabulary"

 

[Chihiro]

Remove this part of the code.

Application.ActiveWorkbook.Close False

It's closing your workbook at first iteration of the loop.

 

[cycouten]

Did not fix the problem I am afraid. Tks.

 

[Chihiro]

Tested on my end. Code worked fine and generated PDF files as intended.

Upload sample if you need further help.

 

[cycouten]

This is interesting. I tried this macro just on one sheet and the same phenomenon appeared. I actually created a shortcut CTL Q to run this macro and it worked !!! so the problem is when I run it in the VBA module something that I don't undertsand happens. So the issue is closed while I have a question mark on what happens ! Tks for your feedback anyway.