CODEWITHSUNDEEP
excelvba
Donovan
Donovan

Reputation: 29

Print Selected Sheets only... but Active Sheet keeps printing instead

I'm trying to loop through all active sheets and save them as separate PDFs. 

dim ws as worksheet
dim path as string
...
For Each ws In ActiveWindow.SelectedSheets
    ws.ExportAsFixedFormat _
        xlTypePDF, _
        Filename:=path & ws.Name, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenafterPublish:=False
Next

It kind of works:

A PDF file is created for each selected sheet in the correct folder... but...

The contents of those PDF files are all the same. It is the Active Sheet being printed each time but with a different filename.

How to fix this? Keep in mind, I only want selected sheets to print.

EDIT: I'm too new to upvote. Thanks for your answers!

Upvotes: 0

Views: 143

Answers (3)

jonrizz
jonrizz

Reputation: 94

you can specify it like this before you export it to pdf enter image description here

Upvotes: 0

Matteo NNZ
Matteo NNZ

Reputation: 12665

You need to Select the sheet before printing out. Just add the command ws.Select right before ws.ExportAsFixedFormat and it will work: 

For Each ws In ActiveWindow.SelectedSheets
    ws.Select '<-- the only thing you have to add.
    ws.ExportAsFixedFormat _
        xlTypePDF, _
        Filename:=path & ws.Name, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenafterPublish:=False
Next

Apart for this:

  • Tip 1: to avoid seeing the sheets jumping over and over, you can add Application.ScreenUpdating = False before the loop starts and then setting it back to Application.ScreenUpdating = True at the end of the loop.
  • Tip 2: for user friendliness, you can get the currently active sheet at the beginning of the loop with Dim currentlySelectedSheet As Worksheet: Set currentlySelectedSheet = ActiveSheet and when the loop ends, you just select it back with currentlySelectedSheet.Select so that the user won't see anything change in their screen when running your macro.

Upvotes: 1

simple-solution
simple-solution

Reputation: 1139

Option Explicit

Sub Save_SelectedSheets_AsPdf()

Dim ws As Worksheet
Dim path As String
Dim actSheet As Worksheet
'...
Set actSheet = ActiveSheet
For Each ws In ActiveWindow.SelectedSheets
    ws.Select
    ws.ExportAsFixedFormat _
        xlTypePDF, _
        Filename:=path & ws.Name, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenafterPublish:=False
Next
actSheet.Select
End Sub

Sub Create_5_Sheets_and_name_them()
    Dim iCt As Integer
    For iCt = 2 To 5
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Sh" & iCt
        Range("A1").Value = ActiveSheet.Name
    Next iCt
End Sub

Upvotes: 0

Related Questions