Selecting all Chart-type Worksheets for Printing to PDF


New Member
Sep 18, 2011
I'd like to print ALL of my charts out to one PDF. In looking around the forums, the solutiion appears to be looping through the worksheets, checking the sheet type, then adding the name to an ever-increasing array, and then identifying the array in a "" method.
So, I've cobbled up the following code, but get an error saying "Subscript out of range" on the last line. Any ideas? Also, rather than using the numeric identifier sheet.type = 3, could I use "xlChart"? I tried that syntax but it didn't like it.
Sub SaveAndPrintPDFAndExcelFiles(ByVal x1 As Excel.Application, strFilename)
Dim i As Integer
Dim j As Integer
strFilename = GetDBPath & "Hydrographs\" & strFilename
x1.Application.DisplayAlerts = False
x1.ActiveWorkbook.SaveAs FileName:=strFilename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
x1.Application.DisplayAlerts = True
Dim Arr()
For i = 1 To x1.Sheets.Count
If x1.Sheets(i).Type = 3 Then
j = j + 1
ReDim Preserve Arr(j)
Arr(j) = Sheets(i).Name
End If
x1.ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:=GetDBPath & "Hydrographs\" & strFilename & ".pdf", OpenAfterPublish:=True
End Sub

Thanks for any help folks can provide!
Jonathan Mulder
Engineering Geologist
California Department of Water Resources

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I use this code to convert certain sheets to pdfs. maybe this can get you started?

you just need to change the sheets name to the sheets that would have charts on them

Option Explicit
Sub RDB_Worksheet_Or_Worksheets_To_PDF()
Dim FileName As String
If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox "There is more than one sheet selected," & vbNewLine & _
"and every selected sheet will be published."
End If
Sheets(Array("Main", "sheet2", "REPORT2")).Select
'Call the function with the correct arguments.
'You can also use Sheets("Sheet3") instead of ActiveSheet in the code(the sheet does not need to be active then).
FileName = RDB_Create_PDF(Sheets("Main"), "c:\Desktop\", True, True)
'For a fixed file name and to overwrite it each time you run the macro, use the following statement.
'RDB_Create_PDF(ActiveSheet, "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)
'If FileName <> "" Then
'Uncomment the following statement if you want to send the PDF by e-mail.
'RDB_Mail_PDF_Outlook FileName, "", "This is the subject", _
"See the attached PDF file with the last figures" _
& vbNewLine & vbNewLine & "Regards Ron de bruin", False
'MsgBox "It is not possible to create the PDF; possible reasons:" & vbNewLine & _
' "Add-in is not installed" & vbNewLine & _
' "You canceled the GetSaveAsFilename dialog" & vbNewLine & _
' "The path to save the file is not correct" & vbNewLine & _
' "PDF file exists and you canceled overwriting it."
' End If

End Sub

Function RDB_Create_PDF(Myvar As Object, FixedFilePathName As String, _
OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String
Dim FileFormatstr As String
Dim Fname As Variant
'Test to see if the Microsoft Create/Send add-in is installed.
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then
If FixedFilePathName = "" Then
'Open the GetSaveAsFilename dialog to enter a file name for the PDF file.
FileFormatstr = "PDF Files (*.pdf), *.pdf"
Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
Title:="Create PDF")
'If you cancel this dialog, exit the function.
If Fname = False Then Exit Function
Fname = FixedFilePathName
End If
'If OverwriteIfFileExist = False then test to see if the PDF
'already exists in the folder and exit the function if it does.
If OverwriteIfFileExist = False Then
If Dir(Fname) <> "" Then Exit Function
End If
'Now export the PDF file.
On Error Resume Next
Myvar.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=Fname, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
On Error GoTo 0
'If the export is successful, return the file name.
If Dir(Fname) <> "" Then RDB_Create_PDF = Fname
End If
End Function
Upvote 0
Thanks for the response MonteCarlo. However, after I hit "send", I noticed a made a slight typo. The error is occuring on the "x1.Sheets(Arr).Select
" line. My problem is in defining the array of "chart-type" worksheets.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back