VBA Code to print sheets between 2 particular sheets

SuperX7

New Member
Joined
Dec 29, 2017
Messages
13
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!
 
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?

Code:
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

Code:
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.
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
This works on a windows system!!!!
Code:
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
 
Upvote 0
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?)
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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."
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top