03-20-2019 09:27 AM
03-20-2019 09:27 AM
Hello All - first time post. Not a rookie to Excel, but no expert either. I am stuck trying to automate a print function.
I have a table which is 4 columns as follows:
Full Name | Rating | Comments | Performed By
In the "Preformed by" field, i have my staff, and there are repeats of staff. So this column (column D) has repeats of "John Smith" (3x), Sam Jones (4x), Mike Adams (1x). Each of the other columns, while there may be repeating information, should be treated as independent data (so a name of Sue Smith, which may repeat once or twice is not related to the important column of "Performed By").
I want to print each range of values for a particular staff person on an individual page (or pages if necessary) to hand out to that person individually. So is staff person John Smith has 5 reviews, that the customer name, the rating and comments all print on a single page, in a table format in the same manner as though I manually selected that range of values and printed using the "Print Selected" function.
I have attached a sample spreadsheet to demonstrate. I need each staff person's reviews, customer name and rating to print on a single page based on the staff persons name. ONE CLICK or FUNCTION
I have 30 staff, each with more than a few ratings to print for them, so I am looking for a way to automate this in some way, and I figure VBA is probably the best option..?
I am open to providing more input as needed. Feel free to ask.
Thanks so Much
03-20-2019 09:03 PM
Hi, @Justin_L .
Here's a quick solution for you. It could still use a few tweaks to the design and practical aspects, but it is doing what you need. Just hit the "staff sheets" button.
Below you can read a summary of the steps that I took:
- Transformed data into an Excel table.
- created a list of unique staff names and put it into a table.
- Tables allow you to add more data to it without the need to update formulas or VBA code to account for the new data range (if they were properly set up).
- To obtain the desired results, make sure that the same staff member is always spelled the same way. (Data validation with a dropdown menu is in place in the last column of the data table.)
- set columns A to D as Print Area
- Set printing scale as fit all columns into one page
- created a macro that goes through the staff list and, for each name filters the data table by that name and then creates a PDF printing of the visible rows. PDF files are saved in the same folder as the Excel file. At the end of the process, the table filters are cleared.
I hope this helps and I wish you and your team lots of fantastic reviews.
03-21-2019 08:14 AM
That worked just perfectly for what I need.... the next question is HOW you got there so I can import it into the ACTUAL report! I run this report anywhere from once a quarter to once a month, and so I will need to repeat this with some regularity! And I will want to make sure it will translate to a new report each time.
I appreciate it though! It works great! Thank you so much! You just saved me HOURS, literally!
03-23-2019 06:46 PM
Hi, @Justin_L. Sorry for the late replay. I am not sure if I understand your question. Can you provide more details about the situation and the problems you are running into?
Where is the data coming from? Which details on the sample situation are different from the real situation?
by Anthony Smith on August 16, 2018