Excel VBA to select specifc worksheets from cells to save as 1 pdf

Question

I was hoping someone could help me.

I've got some code that selects a range of worksheets in one workbook and saves them as one PDF file. At the moment, these sheets are specifically coded into the VBA.

I would like to make this more dynamic and have the code read what worksheets to select from a range of cells with the title of each worksheet I want to select so that this code can be easily used elsewhere if needed.

Alternatively, all my worksheets are in order so that I only need to select the first 14 worksheets, the 15th is called "Stop on PDF", following which is some input files and config pages etc. So maybe some code that selects all of the worksheets until it reaches one with that specific name?

Here is my code so far:

Sub AutoPDFReport()


Dim myDir As String, mySht As String

myDir = "I:\" & Sheets("Config").Range("N7").Value
mySht = Sheets("Config").Range("N9").Value

On Error Resume Next
MkDir myDir
On Error GoTo 0

Sheets(Array("Overview_For_Email", "XXXX", _
    "XXXX", "XXXX", _
    "XXXX", "XXXX", _
    "XXXX", "XXXX", _
    "XXXX", "XXXX", _
    "XXXX", "XXXX", _
    "XXXX", "XXXX")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=myDir & "\" & mySht & Format(Now, "yyyy") & "-" & Format(Now, "mm") & "-" & Format(Now, "dd") & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Worksheets("SalesReportSlim").Select

End Sub

The XXXX's are my worksheets.

Thanks

This is not a free code writing service. Please show the code you already have by editing your question. — Pᴇʜ

Josh Friedlander Josh Friedlander · Accepted Answer · 2017-03-20T10:41:25

This works for me. It uses your second method, cycling through the sheets until it finds "Stop on PDF", and saving as PDFs. Just fill in the correct directory. Note that the string comparison seems to be case-sensitive, unlike most cases in excel, so be careful with how you type it.

Sub ExportToPDF()

    'cycle through sheets in workbook
    For Each ws In ActiveWorkbook.Worksheets

        'save sheetname and check it - if not "stop", then save as pdf
        Dim newname As String
        newname = ws.Name
        MsgBox (newname)
        If newname = "Stop On PDF" Then
            Exit Sub
        End If

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\[put in your address here]\" & newname & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    Next ws

End Sub

Excel VBA to select specifc worksheets from cells to save as 1 pdf

1 Answers