1

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.

Community
  • 1
  • 1
Kakeda
  • 15
  • 1
  • 8

2 Answers2

1

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
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • This does not work, its saying a type mismatch error – Kakeda Mar 15 '15 at 15:09
  • hey thanks for the code above, but is it possible to publish as one document? – Kakeda Mar 15 '15 at 16:55
  • How can i populate the ary based on the sheets where visible rows are greater than 0. I already have the variables counting the num rows, i just need to populate the ary array to incaputre the sheet names and then i can use your code above. – Kakeda Mar 16 '15 at 13:15
  • I would need the code that examines each sheet and determines the number of visible rows. – Gary's Student Mar 16 '15 at 13:20
  • Sheets("Academy").Select ActiveSheet.Range("$A:$U").AutoFilter Field:=1, Criteria1:=Workbooks("ABCSheet.xlsm").Worksheets("4.1").Range("$J$5"), Operator:=xlAnd Acount = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 Sheets("Cambridge").Select ActiveWindow.SmallScroll Down:=-3 ActiveSheet.Range("$A:$T").AutoFilter Field:=1, Criteria1:=Workbooks("ABCSheet.xlsm").Worksheets("4.1").Range("$J$5"), Operator:=xlAnd Bcount = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 and so on..... – Kakeda Mar 16 '15 at 15:35
  • let me know if u need more info? – Kakeda Mar 16 '15 at 15:37
0

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.

Arya
  • 326
  • 2
  • 8
  • 1
    I know but for some other reason i dont want to move the sheets – Kakeda Mar 15 '15 at 14:46
  • then you should create a piece of code to make the sheets in printable order and after the print make the order as it was before... – Arya Mar 15 '15 at 14:51