Workflow Improvements

Use Excel VBA to automate tasks.

Acquisitions and Withdrawals

Problem: Counts of items ordered and items withdrawn had to be calculated by hand and compared to the previous fiscal year. Needed stats for each branch, plus adult and juv, and print vs nonprint. Took hours and was susceptible to human error.

Solution: Gather data from two different workbooks and make counts automatically. Includes error-checking in case items were not cataloged correctly. This makes the count more accurate.

Outcome: More accurate count and the time to completion was reduced to a few minutes.

Clear Holds

Problem: Statistics were being duplicated.

Solution: Rewrote code to format the list, shorten names and barcodes to match hold slip, and changed how the statistics were written to the stats workbook.

Outcome: No more duplicates.

Fund Report

Problem: Several poorly-formatted reports had to be downloaded to track money spent for each location and each vendor.

Solution: Macro takes one downloaded file of all the funds and separates them by department/branch, and by vendor. Does some light calculating to make funds spent easier to see.

Outcome: Instead of downloaded 11 reports, we only have to download one. Saves time.

Invoice Summary

Problem: The invoice summary provided by the ILS (Integrated Library System) replaces invoice numbers with voucher codes, which are useless.

Solution: Macro combines two emails–one with voucher codes and invoice totals, and one with invoice numbers–and creates a report with the invoice numbers and totals.

Outcome: Saves about 45 minutes of time doing this manually every week.

Missing List

Problem: The list generated for missing items belonging to the Main Library is large and unwieldy.

Solution: Separate the items by location within the library and sort by call number. Display the specific status of an item–is it just missing, has it been in transit for a long time, was it claimed returned?

Outcome: Because the list is already divided up, several people can take a part and go check the shelves at the same time.

Order Cards

Problem: An "order card" is created for each item ordered every week (over 1,000), but they were printed out of order.

Solution: Excel macro creates columns that dictate how the cards should be ordered, and Microsoft Access prints them accordingly.

Outcome: Saves hours of time each week re-ordering cards by hand.

Paging List

Problem: The paging list only prints out 5-6 items per page, which is not practical when we have 500-1000 requests for items every day.

Solution: Reformat the list with one item per row, allowing about 40 items to be printed per page. Adds in item barcode, which is helpful when looking for music CDs and determining which edition of a title the patron wants. Like the missing list, it divvies up the items by location within the library so several people can work on it at once.

Outcome: Saves tons of paper and reduces preparation time to a couple minutes. Doing a similar preparation by hand would take about two hours.

Thanks for reading! If you have any comments or questions, feel free to email me at

Business illustrations by Storyset