Print all sheets through CutePDF Writer

Question

I was using .ExportAsFixedFormat from this topic, but unfortunately, Printed PDF had some issues about formatting and some symbols.

I've tried CutePDF Writer and it is OK.

Now, I want to select all sheets and to print as a single file, as the same with .ExportAsFixedFormat function

Is there any shortcut to select all sheets and to print them through VBA to PDF?

I've tried to insert an array, which I populated with sheet names. But I have an error with type mismatch.

Private Sub CommandButton9_Click()

Dim SNarray, i
Dim Filename As String

Filename = "E:\TestMe1.pdf"


ReDim SNarray(1 To Sheets.Count)
For i = 1 To Sheets.Count
    SNarray(i) = ThisWorkbook.Sheets(i).name
Debug.Print SNarray(i)
Next



Application.ActivePrinter = "CutePDF Writer on CPW2:"
Sheets(SNarray()).Select 'Here is the TYPE MISMATCH
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="CutePDF Writer on CPW2:", PrintToFile:=True, Collate:=True, prtofilename:=Filename

End Sub

Thanks!

You didn't type your array, try Dim SNarray() as String. BTW, nice to see that we can select the printer, I've never done that but that may come in handy! :) — R3uK
@R3uK the same line as before..The line with Sheets (SNarray()).Select — Stefan0309

Dick Kusleika Dick Kusleika · Accepted Answer · 2015-10-13T12:54:05

A few things:

Remove the parentheses from your array in your select statement

Sheets(SNarray).Select

Make sure that all of your sheets are visible or you'll get Select method of Sheets class failed error. That's not what you're getting now, but if there is any chance a sheet may be hidden, you might want to code for it. Also, if someone adds a chart sheet or macro sheet and you would want to skip that, you might want to code for that.

Sub test()

    Dim SNarray() As String
    Dim i As Long
    Dim lCnt As Long

    For i = 1 To ThisWorkbook.Sheets.Count
        If ThisWorkbook.Sheets(i).Type = xlWorksheet And _
            ThisWorkbook.Sheets(i).Visible = xlSheetVisible Then

            lCnt = lCnt + 1
            ReDim Preserve SNarray(1 To lCnt)
            SNarray(lCnt) = ThisWorkbook.Sheets(i).Name
        End If
    Next i

    ThisWorkbook.Sheets(SNarray).Select

End Sub

Finally, when you use Sheets without specifying the workbook, you are actually using ActiveWorkbook.Sheets if you're in a standard module and ThisWorkbook.Sheets if you're in the ThisWorkbook module. As long as ThisWorkbook is active, you're fine. But if not, your Sheets.Count may return something different than ThisWorkbook.Sheets(i).Name expects. In general, always qualify your references. A With block is good for that.

Sub test()

    Dim SNarray() As String
    Dim i As Long
    Dim lCnt As Long

    With ThisWorkbook
        For i = 1 To .Sheets.Count
            If .Sheets(i).Type = xlWorksheet And _
                .Sheets(i).Visible = xlSheetVisible Then

                lCnt = lCnt + 1
                ReDim Preserve SNarray(1 To lCnt)
                SNarray(lCnt) = .Sheets(i).Name
            End If
        Next i

        .Sheets(SNarray).Select

    End With

End Sub

Print all sheets through CutePDF Writer

1 Answers