Worksheet Run-Time Error '13': Type Missmatch-CodePudding

i'm trying to save sheets into .pdf and encounter run-time error '13': missmatch. Sometimes if i restarted the excel it worked no error but second time i run it again the errors show. Tried to search same cases but other cases are dealing with datatype. the code :

Sub save_sheet_in_pdf()
    Dim ws As Worksheets
    Dim name_PDF As String
    Dim path_PDF As String

name_PDF = "Test.pdf"
path_PDF = "D:\Users\DIMAS\Documents\Work Documents\Organizational Development\07. PROJECT\" & name_PDF

If Range("Y5") = "PS" Then
Set ws = Worksheets("PensiunA") 'Error while run : Run-Time error '13': Type missmatch
ws.Select
Else
Set ws = Worksheets("PensiunB") 'Sometimes the error detected here
ws.Select
End If



ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path_PDF, Quality:=xlQualityStandart, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Thanks in Advance

CodePudding user response：

Export to PDF Conditionally

Sub ExportToPDF()
    
    Const FolderPath As String = "D:\Users\DIMAS\Documents\Work Documents\" _
        & "Organizational Development\07. PROJECT\"
    Const Filename As String = "Test.pdf"
    Const CriteriaWorksheetName As String = "Sheet1" ' adjust, it is unknown
    Const CriteriaAddress As String = "Y5"
    Const Criteria As String = "PS"
    Const MatchWorksheetName As String = "PensiunA"
    Const NoMatchWorksheetName As String = "PensiunB"
    
    ' If you accidentally forget the folder path's trailing backslash...
    Dim FilePath As String
    If Right(FolderPath, 1) = "\" Then
        FilePath = FolderPath & Filename
    Else
        FilePath = FolderPath & "\" & Filename
    End If
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet

    ' Use 'StrComp' with 'vbTextCompare'
    ' to compare case-insensitively i.e. 'PS = Ps = pS = ps'
    ' (If it needs to be exactly 'PS', use 'vbBinaryCompare').
    If StrComp(CStr(wb.Worksheets(CriteriaWorksheetName) _
            .Range(CriteriaAddress).Value), Criteria, vbTextCompare) = 0 Then
        Set ws = wb.Worksheets(MatchWorksheetName)
    Else
        Set ws = wb.Worksheets(NoMatchWorksheetName)
    End If
    
    ' No need to select or activate, just use the variable ('ws').
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path_PDF, _
        Quality:=xlQualityStandart, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub