Export multiple sheets to PDF

Question

I would like to export the sheets "Verification" and "Deisgn Overview" to a single PDF. Furthest down, the whole code can be seen.

When using

Set wsA = ActiveSheet

the code works fine. However, when I change that definition to

Set wsA = Sheets(Array("Verification", "Design Overview")).Select

the code goes down to the error handling.

Does the multiple sheet selection interfer with the strName or where does the code go wrong?

Also I would like to deselect the multiple sheets and only have the activesheet selected after exporting.

Sub Export()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
'Set wsA = ActiveSheet
Set wsA = Sheets(Array("Verification", "Design Overview")).Select
strTime = Format(Now(), "yyyymmdd\_hhmm")


'Get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'Replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'Create default name for savng file
strFile = "Example"
strPathFile = strPath & strFile

'User can enter name and select folder for the file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
    FileFilter:="PDF (*.pdf), *.pdf", _
    Title:="Select folder and filename to save")

'Export to PDF if a folder is selected
If myFile <> "False" Then
wsA.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=myFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
'Confirmation message with file info
'MsgBox "File has been created: " _
  '& vbCrLf _
  '& myFile
 End If

exitHandler:
Exit Sub
errHandler:
MsgBox "Could not export file", vbCritical, "Export"
Resume exitHandler

ThisWorkbook.Sheets("Design Overview").Select

End Sub

Thanks in advance!

It should be `Set wsA = Sheets(Array("Verification", "Design Overview"))` without the `.Select`. There is no need to select anything in most cases: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). And because wsA is declared as `Worksheet` which is a single worksheet you cannot set it multiple (an array) of worksheets. — Pᴇʜ, May 28 '18 at 09:28
`Dim wsA As Sheets` if you want to set it multiple sheets. Also why the replace into `strName` if you never use that variable anywhere? What are you trying to do here? • `myFile <> "False"` should be `myFile <> False` • Do you plan to export multiple sheets into one PDF or multiple sheets into multiple PDFs (not really clear). • Also `strTime` is never used anywhere. — Pᴇʜ, May 28 '18 at 09:35
I am trying to export multiple sheets into one PDF. If I `Dim wsA As Sheets` and remove the `.Select`, while also removing the `strTime` and `strName` it still does not run. It seems it does not like this part: `wsA.ExportAsFixedFormat _` As if it cannot run with multiple sheets. Thanks for your help! — DFH, May 28 '18 at 09:51
Which error do you get? `ExportAsFixedFormat` should work with multiple worksheets. — Pᴇʜ, May 28 '18 at 09:54
I get this error: "Compile error: Method or data member not found" And the above code is highlighted. — DFH, May 28 '18 at 10:05
using `wsA.Select: ActiveSheet.ExportAsFixedFormat` should work — Pᴇʜ, May 28 '18 at 10:09

score 1 · Answer 1 · answered May 28 '18 at 09:53

If you want to select sheets, based on a variable, this is a nice way to do it:

Dim mySh As Sheets
Set mySh = Worksheets(Array(Worksheets(1).Name, Worksheets(2).Name))
mySh.Select

However, as mentioned in the comments, it is really very rare that you need to Select and Activate objects in Excel - instead you can refer to them, unite them in collections and use them to do whatever you need:

Sub TestMe()

    Dim myWs As Worksheet
    Dim wsCol As New Collection

    wsCol.Add Worksheets(1)
    wsCol.Add Worksheets(2)

    For Each myWs In wsCol
        myWs.Cells(1, 1) = "TEST"
    Next myWs

    Dim mySh As Sheets
    Set mySh = Worksheets(Array(Worksheets(1).Name, Worksheets(2).Name))
    mySh.Select

End Sub

Thus, you get two bonsues - execution speed and better control over the code (thus less errors).

Export multiple sheets to PDF

1 Answers1