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]

Sub Maybe()
Dim i As Long
Dim a As Long, b As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
        If Worksheets(i).Name = "Print>>>>" Then a = Worksheets(i).Index + 1
        If Worksheets(i).Name = "<<<<Print" Then b = Worksheets(i).Index - 1
    Next i
Worksheets.PrintOut From:=a, To:=b
End Sub

Sub Or_So()
    Worksheets.PrintOut From:=Worksheets("Print>>>>").Index + 1, To:=Worksheets("<<<<Print").Index - 1
End Sub

 

[jolivanes]

Sub Save_As_PDF()
Dim i As Long
    For i = Worksheets("Print>>>>").Index + 1 To Worksheets("<<<<Print").Index - 1
        Worksheets(i).PrintOut , , , , , True, , "C:\Folder Name Here\" & Worksheets(i).Name & ".PDF"
    Next i
End Sub

 

[jolivanes]

You have not asked for it but quite regularly it is asked to save the sheets as a single file.

Sub All_Sheets_One_File()
Dim i As Long, shArr
    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, , "C:\Some Folder Name Here\Sheets " & Worksheets(Worksheets("Print>>>>"). _
        Index + 1).Name & " To " & Worksheets(Worksheets("<<<<Print").Index - 1).Name & ".PDF"
    End With
End Sub

 

[SuperX7]

T

Sub Maybe()
Dim i As Long
Dim a As Long, b As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
        If Worksheets(i).Name = "Print>>>>" Then a = Worksheets(i).Index + 1
        If Worksheets(i).Name = "<<<<Print" Then b = Worksheets(i).Index - 1
    Next i
Worksheets.PrintOut From:=a, To:=b
End Sub

Sub Or_So()
    Worksheets.PrintOut From:=Worksheets("Print>>>>").Index + 1, To:=Worksheets("<<<<Print").Index - 1
End Sub

Thanks for taking the time to reply - this doesn't seem to work as it looks like it's trying to print the whole workbook?

 

[SuperX7]

You have not asked for it but quite regularly it is asked to save the sheets as a single file.

Sub All_Sheets_One_File()
Dim i As Long, shArr
    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, , "C:\Some Folder Name Here\Sheets " & Worksheets(Worksheets("Print>>>>"). _
        Index + 1).Name & " To " & Worksheets(Worksheets("<<<<Print").Index - 1).Name & ".PDF"
    End With
End Sub

Thanks Jolivanes, if people are using this file on different computers, is it possible not to have the file save directly and instead have a prompt so the user can choose where to save?
Or alternatively be able to save it to the desktop?

 

[jolivanes]

Re: this doesn't seem to work as it looks like it's trying to print the whole workbook?
Did you change the "Print>>>>" and "<<<<Print" to just having 3 arrows like you have in your first Post?

The following both work for me. Check and change references where required.
Both will save the files to the desktop on any computer without needing to know the computer name.
If you'd rather have a macro where you need to select a folder where to store the results in, let us know. I assume you changed the "Some Folder Name Here" (Post #4) and "Folder Name Here" (Post #3) to the actual Folder names where the files should be saved into.
Oh, don't quote. You're just creating not required junk. Refer to Post numbers if you want to refer to certain actions.

Sub Save_As_PDF_Desktop()
Dim i As Long
    For i = Worksheets("Print>>>>").Index + 1 To Worksheets("<<<<Print").Index - 1
        Worksheets(i).PrintOut , , , , , True, , CreateObject("wscript.shell").specialfolders(4) & "\" & Worksheets(i).Name & ".PDF"
    Next i
End Sub

Sub All_Sheets_One_File_Desktop()
Dim i As Long, shArr
    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, , CreateObject("wscript.shell").specialfolders(4) & "\" & Worksheets(Worksheets("Print>>>>"). _
        Index + 1).Name & " To " & Worksheets(Worksheets("<<<<Print").Index - 1).Name & ".PDF"
    End With
End Sub

BTW, always check all references and change where required and also always try supplied macros on copies of your original. Changes made through code are permanent and irreversible.

Let us know how it goes.

 

[jolivanes]

My Bad.
Yes, macros with "From ..... To" do not work as that is for pages, not for sheets.

After stuffing things up, these are all tested and found to be doing what I think you might be able to use.

Individual sheets to line printer.

Sub Maybe()
Dim i As Long
    For i = Worksheets("Print>>>>").Index + 1 To Worksheets("<<<<Print").Index - 1
        Worksheets(i).PrintOut
    Next i
End Sub

Individual sheets saved as individual PDF files.

Sub Save_As_PDF()
Dim i As Long
    For i = Worksheets("Print>>>>").Index + 1 To Worksheets("<<<<Print").Index - 1
        Worksheets(i).PrintOut , , , , , True, , "C:\Some Folder Name Here\" & Worksheets(i).Name & ".PDF"    '<---- Change to proper folder name
    Next i
End Sub

Individual sheets saved as individual PDF files on desktop.

Sub Save_As_PDF_Desktop()
Dim i As Long
    For i = Worksheets("Print>>>>").Index + 1 To Worksheets("<<<<Print").Index - 1
        Worksheets(i).PrintOut , , , , , True, , CreateObject("wscript.shell").specialfolders(4) & "\" & Worksheets(i).Name & ".PDF"
    Next i
End Sub

All to be saved sheets as a single PDF file. Change the "Some Folder Name Here" to the proper folder name.

Sub All_Sheets_One_File()
Dim i As Long, shArr
    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, , "C:\Some Folder Name Here\Sheets " & Worksheets(Worksheets("Print>>>>"). _
        Index + 1).Name & " To " & Worksheets(Worksheets("<<<<Print").Index - 1).Name & ".PDF"
    End With
End Sub

All to be saved sheets as a single PDF file on desktop.

Sub All_Sheets_One_File_Desktop()
Dim i As Long, shArr
    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, , CreateObject("wscript.shell").specialfolders(4) & "\" & Worksheets(Worksheets("Print>>>>"). _
        Index + 1).Name & " To " & Worksheets(Worksheets("<<<<Print").Index - 1).Name & ".PDF"
    End With
End Sub

All to be saved sheets as a single PDF file on desktop (Different way of doing it).

Sub With_Sheets_Select()
Dim i As Long
Worksheets(Worksheets("Print>>>>").Index + 1).Select
    For i = Worksheets("Print>>>>").Index + 1 To Worksheets("<<<<Print").Index - 1
        Worksheets(i).Select False
    Next i
    ActiveWindow.SelectedSheets.PrintOut , , , , , True, , CreateObject("wscript.shell").specialfolders(4) & "\" & Worksheets(Worksheets("Print>>>>"). _
    Index + 1).Name & " To " & Worksheets(Worksheets("<<<<Print").Index - 1).Name & ".PDF"
End Sub

 

[SuperX7]

Hi, thanks again for taking the time to reply!

Despite changing the sheet reference to correct names I get the following error for any code that saves to desktop:

Run-time error '429': Active X component can't create object (see screenshot attachment)

Not sure how much of a difference it makes but I'm using excel on Mac OSX.

Could you show me me how to save all sheets as one pdf file but ask which folder to store the results in? I think given the circumstances where the directory could change this would be the best route.

 

[jolivanes]

The "Mac" might be the problem here. I don't know anything about Mac computers.
I'll have a look in a while for your request.