PDA

View Full Version : Help with Printing/Exporting First Page of Multiple Sheets to Single PDF file



wannabburly
11-03-2015, 07:34 AM
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
11-03-2015, 11:51 AM
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
11-03-2015, 12:25 PM
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
11-03-2015, 02:14 PM
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
11-05-2015, 07:16 AM
The main thing is that one page. Have you "Set Print Area" on each sheet that you want in the PDF?

Yes.

wannabburly
11-09-2015, 02:33 PM
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
11-09-2015, 08:10 PM
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
11-10-2015, 06:44 AM
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
11-10-2015, 07:49 AM
Did you try it. It should take the sheets named in A3 down using the set ranges.

wannabburly
11-10-2015, 07:53 AM
Just ran it, I get "Subscript out of range" at this line...


Worksheets(SheetsArray).Select

wannabburly
11-10-2015, 01:42 PM
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
11-11-2015, 02:11 PM
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)))