CODEWITHSUNDEEP
vbaexcelpdf
Magnicco
Magnicco

Reputation: 45

Save sheets as pdf

I have this problem trying to print multiple sheets in a single pdf. Browsing online and in the forum i found this code but when i use it i get the ERROR 9 "Subscript out of range" and I don't understand why. I tried in a new workbook the code and it works properly. Can someone help me?

Private Sub cmd_PrintPDF_Click()

ThisWorkbook.Sheets(Array("Costs", "Cars")).Select

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "/" & "Cost&Car", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

This macro runs from the main panel of my workbook, where there are various Command buttons that direct you in requested sheets.When i run this macro the sheet "Cars" is hidden, may be is this the problem? I tried with an .Activate before the code but it still doesn't work.

Upvotes: 1

Views: 1418

Answers (2)

Dawid
Dawid

Reputation: 786

I use this code for export sheets to pdf. Maybe will be useful for you.

Sub SheetsToPdf()

Dim Arr() As String
Dim PdfFileName As String
PdfFileName = "Cost&Car"

ReDim Arr(1, 1)
'sheets name in 1st column, 2nd column for info about visibility sheets
Arr(0, 0) = "Costs"
Arr(1, 0) = "Cars"

Cells(1, 1).Select
For i = LBound(Arr, 1) To UBound(Arr, 1)

Arr(i, 1) = ThisWorkbook.Sheets(Arr(i, 0)).visible ' info about visibility sheets

If Arr(i, 1) = "0" Then 'check visible Sheets "-1" - visible = True, "0" - visible = False
    ThisWorkbook.Sheets(Arr(i, 0)).visible = True
    OrgVisible = False
End If

If i = 0 Then
ThisWorkbook.Sheets(Arr(i, 0)).Select
Else
ThisWorkbook.Sheets(Arr(i, 0)).Select False 'select all sheets with names in arr()
End If  



Next i

'select all data
Cells.Select

'export to pdf
Selection.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
ThisWorkbook.path & "/" & PdfFileName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

'restore old visibility for sheets
For i = LBound(Arr, 1) To UBound(Arr, 1)
    If Arr(i, 1) = "0" Then 'set old visible
        ThisWorkbook.Sheets(Arr(i, 0)).visible = False
    End If
Next i

End Sub

Maybe this simpler version you need? Exports only visible sheets:

Sub SheetsToPdf2()

Dim PdfFileName As String
PdfFileName = "Cost&Car"

Cells(1, 1).Select
For Each Sheets_ In Sheets
    If Sheets_.visible Then
    ThisWorkbook.Sheets(Sheets_.Name).Select False
    End If
Next
'select all data in one sheet
Cells.Select

'export to pdf
Selection.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
ThisWorkbook.path & "/" & PdfFileName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

Upvotes: 1

R3uK
R3uK

Reputation: 14537

Indeed, you can't print an hidden sheet And here is your code without the useless and ressource-greedy Select :

Private Sub cmd_PrintPDF_Click()

ThisWorkbook.Sheets("Costs").Visible = True
ThisWorkbook.Sheets("Cars").Visible = True

ThisWorkbook.Sheets(Array("Costs", "Cars")).ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & "/" & "Cost&Car", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True


ThisWorkbook.Sheets("Costs").Visible = False
ThisWorkbook.Sheets("Cars").Visible = False

End Sub

Upvotes: 1

Related Questions