How to Create an Excel VBA Macro to Print Titles and Descriptions on Separate Pages
Question details
The user needs a VBA macro solution to take a list of titles and descriptions, copy them to a print-ready worksheet, apply specific font sizes, and insert manual page breaks so each entry prints on its own A4 page.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Preparing a batch of records for printing, ensuring every non-empty title and its associated description are styled differently (e.g., 20pt and 14pt fonts) and placed on distinct A4-sized printed pages within one workbook.
- Observed behavior
- Manual layout adjustments and page break insertions are tedious; an automated script is required to handle data retrieval (sometimes via Range.Find), formatting, and pagination seamlessly.
Ensure you have enabled the Developer tab in your spreadsheet software and saved your workbook as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code upon closing.
Write a VBA Macro to Format Print Layout and Insert Page Breaks
Create a macro that loops through your source data, copies it to a destination sheet, applies specific font sizing, sets the paper size to A4, and inserts horizontal page breaks.
This solution assumes you have a source sheet with titles in one column and descriptions in another. The macro will generate a single clean destination sheet, spacing out the content and telling the printer exactly where to split the pages.
Press ALT + F11 on your keyboard to open the VBA editor. From the top menu, select 'Insert' and click 'Module' to create a new blank script window.
In your new module, start by writing code to apply A4 settings to your destination sheet: `ActiveSheet.PageSetup.PaperSize = xlPaperA4`.
Write a `Do While` or `For` loop that iterates through your source data. For each non-empty title, copy it to the destination sheet. Use `Range.Font.Size = 20` for the pasted title cell, and `Range.Font.Size = 14` for the description pasted three rows below it.
After pasting the description, calculate the next empty row and insert a page break using `ActiveSheet.HPageBreaks.Add Before:=Rows(nextRow)`.
Close the VBA editor and run the macro via the Developer tab. Press CTRL + P to view the print preview and confirm each title sits on a separate A4 page.

Use Range.Find to Pull Additional Values from Another Sheet
If you need to include secondary data (like a third value from another worksheet) beneath the description, incorporate the Range.Find method into your loop.
Automate Your Workflows with WPS Spreadsheet Macros
WPS Spreadsheet offers excellent compatibility with Excel VBA macros. You can seamlessly run, edit, and create your VBA scripts to format and print your A4 documents effortlessly.
- 1. Download and Install WPS Office: Visit the official WPS website, download the free suite, and install it on your device.
- 2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the print-formatting VBA macro.
- 3. Access the Macro Editor: Navigate to the 'Tools' tab on the ribbon and click on 'Macros' to view, edit, or run your scripts.
- 4. Run the Print Macro: Select your macro from the list and hit 'Run'. Go to Print Preview to see your neatly separated A4 pages.

Frequently Asked Questions
How do I set the print area to exactly A4 in VBA?
Use the PageSetup object in your macro. Set `ActiveSheet.PageSetup.PaperSize = xlPaperA4` and adjust the margins via the same object to ensure the content fits precisely on one printed page.
Why are my page breaks not inserting correctly via VBA?
Ensure your worksheet is not set to "Fit to Page" in the print settings. Manual page breaks (`HPageBreaks.Add`) will only work properly if the scaling is adjusted to a specific percentage (usually 100%).
Can I loop through multiple titles until an empty cell is reached?
Yes, you can use a `Do While...Loop` and check the cell value. For example, `Do While ActiveCell.Value <> ""` instructs the macro to continue copying and formatting until it hits an empty row.
How do I skip blank rows when running this macro?
Inside your loop, you can add an `If` statement checking if the cell is not empty, such as `If Cells(i, 1).Value <> "" Then`. Place your formatting and copying code inside this block so blank rows are ignored.




