CODEWITHSUNDEEP
vbaexcelexportpdf-generation
Kakeda
Kakeda

Reputation: 15

Print Multiple Excel sheets in a specific order

I am trying to print/publish multiple sheets from Excel workbook, but in a specific order. I use the same code used here but it is not printing in the order I inputted into my array and alternatively is printing from leftmost sheet to the rightmost sheet.

Save multiple sheets to .pdf

I would like to print the sheets in a specific order. I selected the order that I wanted to print, however, it printed from left most sheet and going to right in the way they were in the workbook. How can I make them print in the order that I inputted in the array.

I selected

ThisWorkbook.Sheets(Array("GIT 100", "GIT 399", "CheckList GIT 400", "TCCC", "4.1")).Select

But I got "4.1","CheckList GIT 400","GIT 399","TCCC","GIT 100" as the published document.

Any help would be much appreciated.

Upvotes: 1

Views: 7370

Answers (2)

Gary's Student
Gary's Student

Reputation: 96791

Just loop:

Sub Kakeda()
    ary = Array("GIT 100", "GIT 399", "CheckList GIT 400", "TCCC", "4.1")
    For Each a In ary
        Sheets(a).ExportAsFixedFormat Type:=xlTypePDF
    Next a
End Sub

EDIT#1:

This version will save the .pdf files separately:

Option Explicit

Sub Kakeda()
    Dim ary
    Dim a As Variant, fp As String
    ary = Array("GIT 100", "GIT 399", "CheckList GIT 400", "TCCC", "4.1")
    fp = ActiveWorkbook.Path
    For Each a In ary
        Sheets(a).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fp & "\" & a & ".pdf"
    Next a
End Sub

EDIT#2:

This version will create a single pdf

Option Explicit

Sub Kakeda3_TheSequel()
    Dim ary
    Dim a As Variant, fp As String
    ary = Array("GIT 100", "GIT 399", "CheckList GIT 400", "TCCC", "4.1")
    fp = ActiveWorkbook.Path

    For Each a In ary
        Sheets(a).Move after:=Sheets(Sheets.Count)
    Next a

    ThisWorkbook.Sheets(ary).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF
End Sub

Upvotes: 1

Arya
Arya

Reputation: 341

I think this depends on the order of the sheet, make the sheet order as you want to be printed(in sequence), that will work.

Upvotes: 0

Related Questions