How to Print Selected Student Report Cards with Excel VBA Macro
Question details
The user needs an automated way to print a specific subset of student report cards from a workbook containing up to 55 records, using a macro that references start and end values.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A teacher or administrator is managing a report card workbook and needs to print selectively (e.g., students 10 through 25) without manually selecting each student from a dropdown.
- Observed behavior
- Currently, the user has to either print all records or manually update the dropdown and print each report card individually. The goal is to automate this process via a VBA macro that loops through a defined range.
Ensure your workbook is saved in a Macro-Enabled format (.xlsm) and that you have enabled macros in your spreadsheet application's Trust Center settings.
Create a VBA Macro with a Start and End Loop
Use a VBA script that reads the starting and ending student numbers from specific cells, loops through the designated rows, updates the report card template, and sends each sheet to the printer automatically.
This solution assumes your workbook has a printing control area where cell AD6 contains the start value and AE6 contains the end value. It also assumes that cell AD1 is used as the reference cell (dropdown) that updates the report card data via VLOOKUP.
Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer pane on the left, right-click your workbook name, select 'Insert', and click 'Module'. A blank code window will appear.
Copy and paste the following code into the module window: Sub PrintSelected() Dim startPrint As Long, endPrint As Long, x As Long startPrint = Range("AD6").Value + 2 endPrint = Range("AE6").Value + 2 For x = startPrint To endPrint Range("AD1").FormulaR1C1 = "='Input Data'!R" & x & "C2" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False Next x MsgBox "Job complete" End Sub
Close the VBA editor. Enter your desired start and end numbers in cells AD6 and AE6. Press 'Alt + F8', select 'PrintSelected', and click 'Run' to begin printing the selected report cards.
Seamlessly Run Complex Formulas and Automations in WPS Office
WPS Spreadsheet provides a highly compatible environment for your complex data tasks. It handles VLOOKUP formulas and advanced data referencing flawlessly, ensuring your automated report cards populate correctly every time.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your computer.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open your existing report card workbook.
- 3. Manage Data Smoothly: Utilize WPS Spreadsheet's highly compatible formula engine to populate your dynamic report cards automatically.
Frequently Asked Questions
Why is the macro printing blank or incorrect data?
Ensure that the VLOOKUP formulas in your report card template (e.g., cells E6:M21) correctly reference the dynamic cell (AD1). The macro updates AD1, so your formulas must depend on AD1 to pull the correct student data from the 'Input Data' sheet.
How do I add a button to run the print macro easily?
Go to the Developer tab on your Excel ribbon, click 'Insert', and choose 'Button (Form Control)'. Draw the button on your worksheet, and when the 'Assign Macro' dialog box appears, select 'PrintSelected' and click OK.
Can I save these report cards as PDF files instead of printing them?
Yes. You can modify the macro to export PDFs by replacing the 'PrintOut' line with the 'ExportAsFixedFormat' method. Alternatively, you can temporarily set your computer's default printer to 'Microsoft Print to PDF', which will prompt you to save each file.
