Export multiple sheets into multiple PDF - runt-time error 13

Question

I collect several sheets in an array. Those sheets have not always the same name. Now I want to export each of these sheets into a PDF, one PDF for each sheet. I found some code here on stack overflow but it ends up with run-time error 13 'type mismatch'.

Macro to Export Certain Excel Sheets to Separate PDF

If I select all sheets, in this case from sheet 4 to the last sheet, I manage to export them into one single PDF. So somehow the counting with 'i' is the problem here.

    Dim intSheet As Integer
    Dim intCount As Integer
    Dim intArray As Integer
    Dim varSheet()
    Dim i As Variant, selectedWS As Variant


    intSheet = ActiveWorkbook.Sheets.Count
    intArray = -1
    For intCount = 4 To intSheet
        intArray = intArray + 1
        ReDim Preserve varSheet(intArray)
        varSheet(intArray) = ActiveWorkbook.Sheets(intCount).Name
    Next

selectedWS = Array(varSheet())

For Each i In selectedWS
   ThisWorkbook.Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\R541254\Projects\" & "test" & i & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, OpenAfterPublish:=True

Next i
End Sub

In the end, each sheet should be exportet in a seperate PDF with the name 'test' and the counted number of 'i'.

Thank you in advance and have a great evening :-)

BigBen BigBen · Accepted Answer · 2019-08-25T17:24:50

You don't need an array here:

Dim i as Long

For i = 4 to ThisWorkbook.Sheets.Count
    ThisWorkbook.Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\R541254\Projects\" & "test" & i & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
Next i

Export multiple sheets into multiple PDF - runt-time error 13

1 Answers