VBA for exporting Excel worksheets to PDF

[Devilfish2006k]

Hi guys

I have an Excel workbook that is saved in a folder. What I would like to do is have a VBA routine that loops through all the worksheets that are called 'Payment 1' 'Payment 2' to 'Payment z', export to PDF and save the individual sheets/ PDFs in the same folder as the Excel workbooks as the worksheet tab names. There will be other worksheets that are not called 'Payment' but I want to ignore these

Many thanks

 

[snipescc]

Install ASAP Utilities. It adds a bunch of functions to Excel, one of them being saving each sheet as a separate file. PDF is one of the options for saving the files.

 

[Devilfish2006k]

Thanks for this but is it possible to do with a macro routine?

 

[Logit]

.

Option Explicit


Sub ExportToPDFs()
Dim nm As String
Dim ws As Worksheet
Dim saveInFolder As String
    
    saveInFolder = ThisWorkbook.Path
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    


    For Each ws In Worksheets
       
            If (ws.Name Like "Payment*") Then
                
                    ws.Select
                    nm = ws.Name
            
                    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=saveInFolder & nm & ".pdf", _
                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, OpenAfterPublish:=False
            End If
        
    Next ws
        
End Sub

 

[Devilfish2006k]

Thanks when I run this I get a Run-time error '1004' Document not saved. The document maybe open, or an error may have been encountered when saving.....

 

[Logit]

Do you have data on the sheets named Payment ? It won't work if the sheet is blank.

Otherwise, the macro runs as expected here.

 

[Devilfish2006k]

Do you have data on the sheets named Payment ? It won't work if the sheet is blank.

Otherwise, the macro runs as expected here.

Yes they aren't blank and each tab is called payment 1 then the next payment 2 etc

 

[Logit]

.
Here is a download link for the working example : https://www.amazon.com/clouddrive/share/0xQS9sQH7t1GKLDR5WI1aboJzA5Ra8i1pRDqrEvZxEx