VBA Code to print sheets between 2 particular sheets

[SuperX7]

Hi guys,

I have a code where excel can export the pdf to print sheets that have particular names, but what I want to do is have excel search for particular sheets to print in between 2 particular sheets.

I have a sheet called "Print >>>" and "<<< Print".

There will be sheets created in the future in between these two sheets so I cannot just choose the sheet names (or omit the ones I don't want to print as there will be other new sheets).

Is it possible to have excel print / save pdf all sheets that are in between these two particular sheets?

Thank you!

 

[jolivanes]

In the meantime, get rid of all the previous macros for now and put the following in your module.
This should make it work for both systems.
Try it and let us know as we would like to know if it works also.
From: What is the equivalent of this code for MacOS excel?

Function GetDesktopPath() As String
    #If Mac Then
        GetDesktopPath = Mid(MacScript("tell application ""Finder""" & vbLf & "return desktop as alias" & vbLf & "end tell"), 7)
    #Else
        GetDesktopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    #End If
End Function

Sub All_Sheets_One_File_Desktop()
Dim i As Long, shArr
Path = GetDesktopPath & Application.PathSeparator
    For i = ThisWorkbook.Worksheets("Print>>>>").Index + 1 To ThisWorkbook.Worksheets("<<<<Print").Index - 1
        shArr = shArr & "|" & Worksheets(i).Name
    Next i
    shArr = Split(Mid(shArr, 2), "|")
    With Sheets(shArr)
        .PrintOut , , , , , True, , Path & Worksheets(Worksheets("Print>>>>"). _
        Index + 1).Name & " To " & Worksheets(Worksheets("<<<<Print").Index - 1).Name & ".PDF"
    End With
End Sub

BTW, in the future, let people know that you want code for a Mac only, both Mac and Windows or Windows only.

 

[jolivanes]

This works on a windows system!!!!

Sub All_Sheets_One_File_Select_Folder()
Dim i As Long, FilePath As String, shArr
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then
            FilePath = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    For i = ThisWorkbook.Worksheets("Print>>>>").Index + 1 To ThisWorkbook.Worksheets("<<<<Print").Index - 1
        shArr = shArr & "|" & Worksheets(i).Name
    Next i
    shArr = Split(Mid(shArr, 2), "|")
    With Sheets(shArr)
        .PrintOut , , , , , True, , FilePath & "\" & "Sheets " & Worksheets(Worksheets("Print>>>>"). _
        Index + 1).Name & " To " & Worksheets(Worksheets("<<<<Print").Index - 1).Name & ".PDF"
    End With
End Sub

 

[SuperX7]

In the meantime, get rid of all the previous macros for now and put the following in your module.
This should make it work for both systems.
Try it and let us know as we would like to know if it works also.
From: What is the equivalent of this code for MacOS excel?

Hi, thanks again.

Where does the first part of the code go?

The 2nd part works beautifully but it is printing direct to printer (not what I want) instead of saving to desktop (or asking the user to?)

 

[jolivanes]

Both the Function and the Sub go in a regular module.

Since I don't have a Mac, I have not tested it. Plucked that Function from the Web.

The code from Post #12 saves as PDF for me. I have to look in it but I hope someone with a Mac can try also and change whatever needs to be changed.

My default printer is set to Microsoft Print To PDF so I don't know if that could be the problem. Should not but stranger things have happened.

Maybe "refresh read" line #7 in Post #7.

 

[jolivanes]

The suggestion from Post #12 works like a charm no matter which printer is set as default.

You can try this. It also works like a dandy on my windows system.
If it does not work for you, we'll have to find out how to save to PDF on a Mac.

Sub All_Sheets_One_File_Select_Folder()
Dim i As Long, FilePath As String, PDF As String, cs As Worksheet, shArr
Set cs = ActiveSheet
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then
            FilePath = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    PDF = FilePath & "\" & Worksheets(Worksheets("Print>>>>"). _
        Index + 1).Name & " To " & Worksheets(Worksheets("<<<<Print").Index - 1).Name & ".PDF"
    For i = ThisWorkbook.Worksheets("Print>>>>").Index + 1 To ThisWorkbook.Worksheets("<<<<Print").Index - 1
        shArr = shArr & "|" & Worksheets(i).Name
    Next i
    shArr = Split(Mid(shArr, 2), "|")
    With Sheets(shArr)
        .Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
    End With
cs.Select
End Sub

I found this on the net though where someone wanted to do the same as you on a Mac.
"What's important is that you can't save your file to a location that is selected by you."

 

[jolivanes]

From: Save as pdf using Mac Excel VBA
Here is a guide on how you can do it: Home | Ron de Bruin Excel Automation
What's important is that you can't save your file to a location that is selected by you.
It has to be saved to the folder Library/Group Containers/UBF8T346G9.Office under the current user's home dir, so /Users/[current user]/Library/Group Containers/UBF8T346G9.Office in most of the cases. If the folder is not there, you have to create it. (See the code example on the page linked above)
Kudos to Ron!

This goes beyond what I can help you with for obvious reason.
A long workaround might be if you save the file as mentioned above and then with code move it to the Folder selected.