VBA to save Excel files as PDF

Please help me with the following Excel Macro. I currently have it set up as if given a specific folder. It will either convert all the excel (*.xsl* ) inside the folder into PDF. I was able to either the active/selected sheet or all of them.

Please see current code below:

    Sub Convert_Excel_To_PDF()

        Dim MyPath As String, FilesInPath As String

        Dim MyFiles() As String, Fnum As Long

        Dim mybook As Workbook

        Dim CalcMode As Long

        Dim sh As Worksheet

        Dim ErrorYes As Boolean

        Dim LPosition As Integer

        'Fill in the path\folder where the Excel files are

        MyPath = "C:\downloads\example_test\"

        FilesInPath = Dir(MyPath & "*.xls*")

        If FilesInPath = "" Then

            MsgBox "No files found"

            Exit Sub

        End If

        Fnum = 0

        Do While FilesInPath <> ""

            Fnum = Fnum + 1

            ReDim Preserve MyFiles(1 To Fnum)

            MyFiles(Fnum) = FilesInPath

            FilesInPath = Dir()

        Loop

        With Application

            CalcMode = .Calculation

            .Calculation = xlCalculationManual

            .ScreenUpdating = False

            .EnableEvents = False

        End With

        If Fnum > 0 Then

            For Fnum = LBound(MyFiles) To UBound(MyFiles)

                Set mybook = Nothing

                On Error Resume Next

                Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))

                On Error GoTo 0

                If Not mybook Is Nothing Then

                        LPosition = InStr(1, mybook.Name, ".") - 1

                        mybookname = Left(mybook.Name, LPosition)

                        mybook.Activate

                        'All PDF Files get saved in the directory below:

                        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

                            "C:\downloads\example_test\" & mybookname & ".pdf", _

                            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _

                            :=False, OpenAfterPublish:=False

                End If

                mybook.Close SaveChanges:=False

            Next Fnum

        End If

        If ErrorYes = True Then

            MsgBox "There are problems in one or more files, possible problem:" _

                 & vbNewLine & "protected workbook/sheet or a sheet/range that not exist"

        End If

        With Application

            .ScreenUpdating = True

            .EnableEvents = True

            .Calculation = CalcMode

        End With

    End Sub

I am trying to do it so that based on a table (with the name "sheet_select_table1"), it does a lookup of the name, and if it matches column A, then only use the sheet on column B and convert such specific sheet number into PDF.

For example:

If there is a file with the name "04-file1.xls" it would go through the given path and only turn into PDF sheet 1. Similarly, if there is a file "08-test2.xlsx" it would only turn into PDF sheet 2.

Also, just to clarify, the macro_file.xslm will be the one running to convert other excel files (in the given folder) into PDFs. There will be no content in the macro file other than the table indicated above, so there is no need to convert it into PDF.

If the files do not match the name, import all the sheets (which is what it is currently doing).

I think it would have to have an IF function to go over the table.

Any help is appreciated!

Please let me know if anything needs further clarification; again, thank you in advance!

SPAM/Advertising Virus/Spyware/Malware danger Other Term of Use or Code of Conduct violation Child exploitation or abuse Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.

If Error > 0 Then                    'added     
    SheetNumber = 0                  'added 
End If                               'added  
It is Err not Error. Change it to 
If Err > 0 Then                    'added     
  SheetNumber = 0                  'added 
End If                               'added    

See https://1drv.ms/x/s!Avg5eRPJ5YjoiD6qLCuY5jLqtAJ1?e=4jYJf2

Note:

This can go within the If Err > 0 check:

Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) ' copied
this will avoid opening the file when it is not to be processed

If this response answers your question then please mark as Answer. It helps others who browse. SPAM/Advertising Virus/Spyware/Malware danger Other Term of Use or Code of Conduct violation Child exploitation or abuse Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.