• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create a Macro to Print Reports to PDF using selected entries from a roster of clients

B

blairski10

New Member
Hello,

I was recently searching for suitable macro code to print multiple sheets in a workbook for multiple clients and came across this forum thread:

http://forum.chandoo.org/threads/cr...sing-entries-in-a-data-validation-list.11701/

Here's what I'm hoping to accomplish:

I have a fixed multi-sheet Excel 2010 workbook that will populate certain data on 7 separate worksheets. These 7 worksheets are intended to be presented as a client specific report for each client. Right now I'm going through my client list, entering the number into a data cell, and then hitting a print macro which I've already installed that prints all of the 7 sheets to PDF. It's incredibly time consuming as I need to run these reports regularly for over 100 clients.

I would like to automate my workbook printing to do the following:

1) Print all 7 sheets with the data populated for each client, as specified by an "X" next to the client name on a roster included within a separate sheet in the workbook
2) Repeat the process for all selected clients
3) Save each report in a specified folder of my choosing on my computer, with a file name reflecting the client name or ID # and the date it was run.

Is this possible? How would the previously mentioned code in the link above be modified?

Many Thanks!

Blair G.
 
Thanks Hui,

I read the threads in your post above. Although those are excellent tools and I will definitely try to incorporate them into my future models, my problem is that I need to figure out how to loop a print macro for certain sheets, based on the number of client IDs I wish to put in a print list.

So for example I have a list of 10 clients, with ID numbers in column A and client names in column B. My model populates by entering in the client ID into a single cell. What I want to accomplish is the macro to grab the first ID, populate the model, print the report to PDF and save it to a desired location with the name of the client and date as the name, then move onto the next client ID in the list.

Or....

Have a set list of client IDs and use an "X" in Column C of the same list to decide which ones to print.

Can this be done?

Here's the macro code I'm using currently to print my dashboards:

Private Sub Dashboard_Print_Click()

ThisWorkbook.Sheets(Array("Dashboard", "Evaluation Master", "Near-Term Evaluation", "Long-Term Evaluation", "Graphs", "Composite Score", "Composite Score Explanation")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Clients\Fiscal Dashboard\Printouts\tempo.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

Thanks again for your attention to this. Have a great day.

Blair G.
 
it will be something like:

Code: 
Private Sub Dashboard_Print_Click()

Dim Client As Range
For Each Client In Range("c1: c20") 'Adjust to suit
  If Client.Value = "" Then Exit For

  'add code here to put the client name client.Value into various cells before printing

  ThisWorkbook.Sheets(Array("Dashboard", _
  "Evaluation Master", _
  "Near-Term Evaluation", _
  "Long-Term Evaluation", _
  "Graphs", _
  "Composite Score", _
  "Composite Score Explanation")).Select

  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  "C:\Clients\Fiscal Dashboard\Printouts\tempo.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, OpenAfterPublish:=True

Next Client

End Sub
 
Hi

I do this all the time with my work. I would go about this a bit differently. The sheets you want included in the PDF – put them on the far left of your spreadsheet lined up from 1 to 7. Now run this code over it, where you run this on the sheet where the client list lives in Col C.

Code: 
Sub Goski()
Dim i As Integer

    For i = 2 To Range("C" & Rows.Count).End(xlUp).Row
        Sheet1.[a1] = Range("C" & i).Value
        Sheets(Array(1, 2, 3, 4, 5, 6, 7)).Select
        ActiveSheet.ExportAsFixedFormat xlTypePDF, "D:\" & Range("C" & i).Value & ".pdf"
    Next i
End Sub

Change the Path to suit and perhaps where the cell that changes the data on all 7 sheets (A1 of the sheet1 above). Protects you from the sheet names changing.

Take care

Smallman
 
You must log in or register to reply here.
Back
Top