Macro to create PDF's

[eoghanmolloy]

Hi,

I have an Excel Workbook with c. 200 worksheets.

Is there a macro I can use that will create a PDF per worksheet, saved as per the name of the worksheet and saved onto my desktop?

Thanks
Eoghan

[AlphaFrog]

Sub Save_Sheets_PDF()
    
    Dim strPath As String
    Dim ws As Worksheet
    Dim counter As Long
    
    strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\PDF Sheets\"
    If Not Len(Dir(strPath, vbDirectory)) Then MkDir strPath
    
    For Each ws In Worksheets
        If ws.UsedRange.Count > 1 Then
        
            ws.ExportAsFixedFormat _
               Type:=xlTypePDF, _
               Filename:=strPath & ws.Name & ".pdf", _
               Quality:=xlQualityStandard, _
               IncludeDocProperties:=True, _
               IgnorePrintAreas:=False, _
               OpenAfterPublish:=False
               
            counter = counter + 1
        End If
    Next ws
    
    MsgBox counter & " files saved.", vbInformation, "PDF Files Save Complete"
    
End Sub

[eoghanmolloy]

This is great. Thank you very much.