Exporting specific sheets to PDF

Question

I have a table called Overview which has a column that represents sheet names I wish to export as a single PDF. the msgbox returns printarray as the correct text. if I type the text in manually to the 'Sheets(Array(Printarray)).Select' line it will return a single PDF.

but form then on I am stuck.... please help..

Sub ExportAsPDF()
Dim FolderPath As String
FolderPath = ActiveWorkbook.Path
Dim tbl As ListObject
Set tbl = Sheets("Overview").ListObjects("Overview")
DStot = tbl.DataBodyRange.Rows.Count

For DS = 2 To DStot + 1 Step 1
    If DS = DStot + 1 Then
        Printarray = Printarray & Chr(34) & Sheets("Overview").Range("A" & DS).Value & Chr(34)
    ElseIf DS = 2 Then
        Printarray = Chr(34) & Sheets("Overview").Range("A" & DS).Value & Chr(34) & ","
    Else
        Printarray = Printarray & Chr(34) & Sheets("Overview").Range("A" & DS).Value & Chr(34) & ","
    End If
Next DS

msgbox(Printarray)

    Sheets(Array(Printarray)).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _
        openafterpublish:=True, ignoreprintareas:=False
   
Sheets("config").Select
End Sub

Your issue is that you are passing a single string `"Sheet2, Sheet3"` into `Array()` instead of the method shown [here](https://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf). Checkout [this](https://www.tachytelic.net/2019/09/vba-add-item-array/) and see if that works. Hope that works! — OGP99, Jun 17 '22 at 14:44
Sub ExportAsPDF() Set VbaArrayNames = CreateObject("System.Collections.Arraylist") Dim FolderPath As String FolderPath = ActiveWorkbook.Path Dim tbl As ListObject Set tbl = Sheets("Overview").ListObjects("Overview") DStot = tbl.DataBodyRange.Rows.Count For DS = 2 To DStot + 1 Step 1 VbaArrayNames.Add Sheets("Overview").Range("A" & DS).Value Next DS Sheets(VbaArrayNames).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", openafterpublish:=True, ignoreprintareas:=False Sheets("config").Select End Sub — RMLAND, Jun 20 '22 at 07:39
Sub ExportAsPDF() Set VbaArrayNames = CreateObject("System.Collections.Arraylist") Dim FolderPath As String FolderPath = ActiveWorkbook.Path Dim tbl As ListObject Set tbl = Sheets("Overview").ListObjects("Overview") DStot = tbl.DataBodyRange.Rows.Count For DS = 2 To DStot + 1 Step 1 Sheets(Sheets("Overview").Range("A" & DS).Value).Select False Next DS ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _ openafterpublish:=True, ignoreprintareas:=False Sheets("config").Select End Sub — RMLAND, Jun 20 '22 at 07:54

score 0 · Answer 1 · answered Jun 20 '22 at 07:55

Sub ExportAsPDF() Set VbaArrayNames = CreateObject("System.Collections.Arraylist") Dim FolderPath As String FolderPath = ActiveWorkbook.Path Dim tbl As ListObject Set tbl = Sheets("Overview").ListObjects("Overview") DStot = tbl.DataBodyRange.Rows.Count

For DS = 2 To DStot + 1 Step 1 Sheets(Sheets("Overview").Range("A" & DS).Value).Select False Next DS

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _
    openafterpublish:=True, ignoreprintareas:=False

Sheets("config").Select End Sub

Exporting specific sheets to PDF

1 Answers1