VBA multi-report to one pdf

In Excel 2010 I have vba code that loads relecant data into an array that creates a couple hundred reports for individuals with their specific information. (no problem with this).

Initially the request was to "print" these reports - for which the following was successfully used:
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False

Now the request has changed to print all the reports into 1 pdf with pagebreaks for each report.  I am totally stumped how to proceed to create a pdf with all the reports.  As stated above, these reports are created on the fly and don't exist to attach.

Any input as to how to proceed on changing the output (print) function to pdf is needed.
uadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

you could try by creating a temp file

adapt tmpFile and Destination Filename
Sub macro()

ActiveWindow.SelectedSheets.Copy
tmpFile = "c:\tmpFile.xls"
With ActiveWorkbook
    .SaveAs tmpFile
    .ExportAsFixedFormat Type:=xlTypePDF, _
                             Filename:="c:\test.pdf", _
                             Quality:=xlQualityStandard, IncludeDocProperties:=False, _
                             IgnorePrintAreas:=True, _
                             OpenAfterPublish:=False
    .Close
      
End With
Kill tmpFile
End Sub

Open in new window

Regards
0
uadAuthor Commented:
The problem with the suggestioned code above is that it creates each individual report as a separate pdf.  I need to get all the reports into "one" pdf file.
0
Rgonzo1971Commented:
Will some sheets be repeated?
And does the excel already have all sheets
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

uadAuthor Commented:
The macro populates the each individual one a a time and prints each report after it is created.  The "template" is the same for all reports and is reused for each report.

In other words it is something like this:
for x = 1 to ubound(array())
  sheets ("statement").select
  fill in the data
  format various rows & columns
  activewindow.selectedsheets.printout copies:=1,  _
     collate:=true, IgnorePrintAreas:=false

  range ("a13:ak500").select
  Selection.delete shift:=xlUp
  selection.rowheight = 17
next x
 
The macro works and the reports are generated.

As stated initially in need to convert the
   activewindow.selectedsheets.printout copies:=1,  _
     collate:=true, IgnorePrintAreas:=false

to print all the reports into a single pdf file.

The sample code suggested created a pdf for each report overwritting the prior pdf not appending the new report to the existing pdf
0
Rgonzo1971Commented:
Then create a tmp file and populate it with your statements and the create a pdf befor closing without saving your xl file

Sub Macro2()

Dim DestPrtWbk As Workbook
Set DestPrtWbk = Workbooks.Add
ThisWorkbook.Activate
For x = 1 To UBound(Array())
  Sheets("statement").Select
  'fill in the data
  'format various rows & columns
  
  Sheets("Statement").Copy After:=DestPrtWbk.Sheets(DestPrtWbk.Sheets.Count)
  ThisWorkbook.Activate
  If x = 1 Then ' delete unnecessary sheets
    For Idx = DestPrtWbk.Sheets.Count - 1 To 1 Step -1
        Application.DisplayAlerts = False
        DestPrtWbk.Sheets(Idx).Delete
        Application.DisplayAlerts = False
    Next
  End If


  With Range("a13:ak500")
    .Delete shift:=xlUp
    .RowHeight = 17
  End With
Next x

DestPrtWbk.ExportAsFixedFormat Type:=xlTypePDF, _
                             Filename:="\\chca6037.eur.beluni.net\a853725$\Documents\test.pdf", _
                             Quality:=xlQualityStandard, IncludeDocProperties:=False, _
                             IgnorePrintAreas:=False, _
                             OpenAfterPublish:=False

     
DestPrtWbk.Close False
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
uadAuthor Commented:
Currently there are 2 workbooks that the macro uses:
1)  Master.xlsm which contains the statement template along with sheets for additional legends and footnotes
2) Macro.xlm which contains the code example used

When I run the code suggested, it is using the Master.xlsm (where the statement template is) as the DestPrtWbk - which isn't good.

Do I need to set DestPrtWbk to a 3rd workbook  and activate it at
Sheets("Statement").Copy After:=DestPrtWbk.Sheets(DestPrtWbk.Sheets.Count) ?  If so, how would ThisWorkbook.activate be affected. Neither Master.xlsm or Macro.xlsm should be modified in any way.
0
uadAuthor Commented:
Please ignore my prior issue.  I figured out what I needed to do which was to activate the Master.xlsm and select the "statement" sheet  prior to executing the delete.
0
uadAuthor Commented:
Great and simple solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.