CODEWITHSUNDEEP
vbaexcel
DFH
DFH

Reputation: 45

Export multiple sheets to PDF

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!

Upvotes: 1

Views: 343

Answers (1)

Vityata
Vityata

Reputation: 43565

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).

Upvotes: 1

Related Questions