Loop through all excel files in folder, save down certain worksheets as PDF with same name

  • Hi everyone, in the process of learning VBA and appreciate help on these great forums..

    I have many xlsx files located in a folder, I'd like to:

    Loop through each one, select the first 6 sheets, and save down as a PDF with the same name (can be in same directory or in a new folder)

    For example Lemon,Liz.xlsx has about 10 tabs (could be more added later) but I want only the first 6 (will always be located first) to be saved down as Lemon,Liz.pdf then loop to the next name.

    Thank you for any guidance.

    Also, I posted this question here as I'm in a bit of a pinch and spreading out the resources


  • Awesome! This works great! One small thing is when the file names are saved it includes the folder location in the PDF name. I tried a couple things and can't seem to make it go away..

    So "TESTLemon, Liz.pdf"

  • remove myPath &

    1. ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    2. Replace(f.Name, ".xlsx", ".pdf"), Quality:=xlQualityStandard, _
    3. IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    4. True
  • if sheets are less than 6 it won't close the excel file so in else statement use

    1. Else
    2. MsgBox "File: " & f.Name & " has fewer than 6 sheets and will be skipped"
    3. Workbooks(f.Name).Close savechanges:=False
  • Thank you that seems to have worked :), I also added UpdateLinks:=False below for good measure..

    1. For Each f In Fldr
    2. If f.Name Like "*.xlsx" Then
    3. Workbooks.Open Filename:=f, UpdateLinks:=False
    4. With Workbooks(f.Name)