I have a workbook with 100 sheets and I want to quickly save each sheet as its own PDF file. I was able to find some instructions, but the code keeps giving me an error at the highlighted spot. Any insight would be appreciated.


Sub RDB_Worksheet_Or_Worksheets_To_PDF() 

Dim FileName As String 


If ActiveWindow.SelectedSheets.Count > 1 Then 

MsgBox "There is more than one sheet selected," & vbNewLine & _ 

"and every selected sheet will be published." 

End If 


'Replace numSheets with the number of worksheets that will be saved as PDF 

For x = 1 To numSheets 

Sheets("WorksheetNames").Select 

ThisSheet = ActiveSheet.Range("A" & x).Value 


Sheets(ThisSheet).Select 

'Call the function with the correct arguments 

FileName = RDB_Create_PDF(Sheets(ThisSheet), "C:\test\" & ActiveSheet.Name & ".pdf", True, True) 


If FileName <> "" Then 

'Ok, you find the PDF where you saved it 

Else 

MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _ 

"Microsoft Add-in is not installed" & vbNewLine & _ 

"You Canceled the GetSaveAsFilename dialog" & vbNewLine & _ 

"The path to Save the file in arg 2 is not correct" & vbNewLine & _ 

"You didn't want to overwrite the existing PDF if it exist" 

End If 

Next x 

End Sub 


Function RDB_Create_PDF(Myvar As Object, FixedFilePathName As String, _ 

OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String 

Dim FileFormatstr As String 

Dim Fname As Variant 


'Test If the Microsoft Add-in is installed 

If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _ 

& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then 


If FixedFilePathName = "" Then 

'Open the GetSaveAsFilename dialog to enter a file name for the pdf 

FileFormatstr = "PDF Files (*.pdf), *.pdf" 

Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _ 

Title:="Create PDF") 


'If you cancel this dialog Exit the function 

If Fname = False Then Exit Function 

Else 

Fname = FixedFilePathName 

End If 


'If OverwriteIfFileExist = False we test if the PDF 

'already exist in the folder and Exit the function if that is True 

If OverwriteIfFileExist = False Then 

If Dir(Fname) <> "" Then Exit Function 

End If 


'Now the file name is correct we Publish to PDF 

On Error Resume Next 

Myvar.ExportAsFixedFormat _ 

Type:=xlTypePDF, _ 

FileName:=Fname, _ 

Quality:=xlQualityStandard, _ 

IncludeDocProperties:=True, _ 

IgnorePrintAreas:=False, _ 

OpenAfterPublish:=False 

On Error GoTo 0 


'If Publish is Ok the function will return the file name 

If Dir(Fname) <> "" Then RDB_Create_PDF = Fname 

End If 

End Function