CODEWITHSUNDEEP
vbaexcel
Sean Pakulski
Sean Pakulski

Reputation: 29

Save As PDF VBA Function issue

I made a simple function to save all my tabs into a destination folder. However, after implementing the total process I realized this won't work week to week.

To solve the issue I'm having from one week to another, I need the files to be pdfs instead of .xlsx. I attempted to fix this myself by modifying the already existing function, however, when I open the pdfs it generates, they are "corrupted" or so says Adobe.

Please see below:

Sub SaveFilesInFolder()
'
'This is a function for saving each worksheet as a workbook in a destination folder
'

'
    Dim sh As Worksheet
    Dim wb As Workbook

    For Each sh In Worksheets
        SheetName = sh.Name
        sh.Copy

        With ActiveWorkbook
            .SaveAs FileName:="C:\Example\" & SheetName & ".pdf"
            .Close SaveChanges:=True
        End With

    Next sh

End Sub

I am aware of ExportAsFileFormat but I am having trouble getting that to work with the entire workbook that has 100+ tabs.

Any recommendations are appreciated.

Thank you,

Upvotes: 0

Views: 1035

Answers (2)

Abe Gold
Abe Gold

Reputation: 2347

Sub Test()
For Each sht In Sheets
  sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\" & sht.Name & ".pdf"
Next
End Sub

Upvotes: 1

Kostas K.
Kostas K.

Reputation: 8518

You need to call the ExportAsFixedFormat method.

.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Example\" & SheetName & ".pdf"

Upvotes: 0

Related Questions