Change code to only print some worksheets

Question

I am using the highly complicated yet useful code from Ron de Bruin on how to print to PDF on Mac Excel. I am using the following code:

Sub PublishEachWorkSheetToPDFInMacExcel()
    'Ron de Bruin : 11-Dec-2020
    'Test macro to publish each worksheet to pdf with ExportAsFixedFormat
    'Note : if set it save the printarea
    'It will create a new folder for you with the files
    Dim FolderName As String
    Dim Folderstring As String
    Dim Fstr As String
    Dim TestStr As String
    Dim sh As Worksheet
    Dim FileName As String
    Dim FilePathName As String

    'Name of the Root folder in the Office folder, and create the folder
    FolderName = "PDFSaveFolder"
    Folderstring = CreateFolderinMacOffice(NameFolder:=FolderName)

    'Create folder in the Root folder with the name of the ActiveWorkbook
    Fstr = Mid(ActiveWorkbook.Name, 1, InStrRev(ActiveWorkbook.Name, ".", , 1) - 1) & Format(Now, " dd-mmm-yyyy hh-mm-ss")
    On Error Resume Next
    TestStr = Dir(Folderstring & "/" & Fstr, vbDirectory)
    On Error GoTo 0
    If TestStr = vbNullString Then MkDir Folderstring & "/" & Fstr

    'Loop through all worksheets
    For Each sh In ActiveWorkbook.Worksheets
        'If the sheet is visible then publish it to PDF
        If sh.Visible = -1 Then
            sh.PageSetup.Orientation = sh.PageSetup.Orientation

            'File name is the sheet name and a date/time stamp
            FileName = sh.Name & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss") & ".pdf"

            'Publish the Worksheet to pdf
            FilePathName = Folderstring & Application.PathSeparator & Fstr & Application.PathSeparator & FileName
            'expression A variable that represents a Workbook, Sheet, Chart, or Range object.
            'the parameters are not working like in Excel for Windows
            sh.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            FilePathName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False
        End If
        Next sh
        
    MsgBox "You find the PDF files in this location : " & Folderstring & "/" & Fstr
End Sub
Function CreateFolderinMacOffice(NameFolder As String) As String
    'Function to create folder if it not exists in the Microsoft Office Folder
    'Ron de Bruin : 13-July-2020
    Dim OfficeFolder As String
    Dim PathToFolder As String
    Dim TestStr As String

    OfficeFolder = MacScript("return POSIX path of (path to desktop folder) as string")
    OfficeFolder = Replace(OfficeFolder, "/Desktop", "") & _
        "Library/Group Containers/UBF8T346G9.Office/"

    PathToFolder = OfficeFolder & NameFolder

    On Error Resume Next
    TestStr = Dir(PathToFolder & "*", vbDirectory)
    On Error GoTo 0
    If TestStr = vbNullString Then
        MkDir PathToFolder
        'You can use this msgbox line for testing if you want
        'MsgBox "You find the new folder in this location :" & PathToFolder
    End If
    CreateFolderinMacOffice = PathToFolder
End Function

I want to change the code so I only print some of the worksheets. But whenever I change the for loop I run into problems with the sh

How do I get the loop to start from sheet 14 and run until end of worksheets?

Change code to only print some worksheets

0 Answers