Help with Printing/Exporting First Page of Multiple Sheets to Single PDF file

[wannabburly]

I have a workbook that creates/updates sheets daily.

I am looking for a macro I can assign to a user control that will print the first page of all sheets whose names falls within a range of data. I need the pages printed/exported into one single PDF file with multiple pages so I can distribute via email.

The range of data is on my Input sheet which I don't want printed. There are also 5 static sheets (Input included) that I don't want printed.

Thanks in advance.

[Kenneth Hobs]

Without seeing your data, it is hard to help. Adapt this to suit.

'http://www.excelforum.com/excel-programming-vba-macros/1107003-continuous-page-numbers-when-printing-to-pdf-via-vba.html
Sub shg()
  Dim r As Range, c As Range, tf As Boolean, pdfFile As String
  
  pdfFile = ThisWorkbook.Path & "\Print Control.pdf"
  
  With Worksheets("Print Control")
    Set r = .Range("A1", .Range("A1").End(xlDown))
    For Each c In r
      If c.Value = "Yes" Then
        If tf = False Then
          Worksheets(c.Offset(, 1).Value2).Select Replace:=True
          tf = True
          Else
          Worksheets(c.Offset(, 1).Value2).Select Replace:=False
        End If
      End If
      Debug.Print c.Address, c.Offset(, 1).Value, tf
    Next c
    
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFile
    .Select
  End With
  
  Shell "cmd /c " & """" & pdfFile & """"
End Sub

[wannabburly]

Thanks Kenneth. Sorry, I guess I could have provided more details...

I have a range "JobNumbers" which is JobNumbers = Worksheets("Input").Range("A3", Range("A3").End(xlDown)).
My workbook already creates sheets every time a new Job Number is added.
These sheets are copies of a template. The template is formatted such that the first page contains charts and tabular data that serve as a daily project update. Beyond the first page are data tables that populate the charts and do not need to be printed.


I need to select only the worksheets whose names match data in the JobNumbers range, print the first page only, and combine all of these pages into a single PDF file.

Does that clear anything up?

[Kenneth Hobs]

The code would be similar. The concept used in the code is to use the Replace:=True option for that first sheet and False for the others. There are other ways depending on your print order, sheet order, sheet visibility, and such.

The main thing is that one page. Have you "Set Print Area" on each sheet that you want in the PDF?

[wannabburly]

The main thing is that one page. Have you "Set Print Area" on each sheet that you want in the PDF?

Yes.

[wannabburly]

Kenneth, can you help me adapt the code such that I don't have to add a "print control" column in front of my Job Numbers column?

[Kenneth Hobs]

I guess you want something like:

' http://www.vbaexpress.com/forum/search.php?searchid=44632
  Sub pdfSheets2()
    Dim SheetsArray() As Variant
    Dim fName As String
    Dim fPath As String
     
    fPath = "c:\temp\"
    fName = "pdfSheets2.pdf"
     
    SheetsArray() = WorksheetFunction.Transpose(Worksheets("Input").Range("A3", _
      Worksheets("Input").Range("A3").End(xlDown)))
    Worksheets(SheetsArray).Select
     
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=fPath & fName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
End Sub

[wannabburly]

I guess you want something like:

' http://www.vbaexpress.com/forum/search.php?searchid=44632
  Sub pdfSheets2()
    Dim SheetsArray() As Variant
    Dim fName As String
    Dim fPath As String
     
    fPath = "c:\temp\"
    fName = "pdfSheets2.pdf"
     
    SheetsArray() = WorksheetFunction.Transpose(Worksheets("Input").Range("A3", _
      Worksheets("Input").Range("A3").End(xlDown)))
    Worksheets(SheetsArray).Select
     
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=fPath & fName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
End Sub

That would put all of the sheets into the array, but does that help me with only printing the first page of each selected sheet?

[Kenneth Hobs]

Did you try it. It should take the sheets named in A3 down using the set ranges.

[wannabburly]

Just ran it, I get "Subscript out of range" at this line...

    Worksheets(SheetsArray).Select

[wannabburly]

Kenneth,

I made a mistake regarding print area vs page layout, so I apologize for questioning you previously about the first page of each sheet. I still get the subscript out of range error.

Do I need to put the code somewhere specific? I have tried my print macro in its own module and in ThisWorkbook, but I can't get it to work.

I now get a system error saying filename, directory name, or volume label syntax is incorrect. Yeesh...

[Kenneth Hobs]

Does the sheet name Input in this line exist and does the range start at A3 and do all of the sheetnames from A3 down exist? You can change those to suit your needs.

SheetsArray() = WorksheetFunction.Transpose(Worksheets("Input").Range("A3", _ 
    Worksheets("Input").Range("A3").End(xlDown)))