VBA & Macro Problems

How to Create an Excel VBA Macro to Print Titles and Descriptions on Separate Pages

Adam DavisAdam Davis Nov 2, 2026 870 views

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.

How to Create an Excel VBA Macro to Print Titles and Descriptions on Separate Pages
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the Visual Basic Editor

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.

2
Define the Page Setup for A4

In your new module, start by writing code to apply A4 settings to your destination sheet: `ActiveSheet.PageSetup.PaperSize = xlPaperA4`.

3
Create a Loop to Copy and Format Data

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.

4
Insert Manual Page Breaks

After pasting the description, calculate the next empty row and insert a page break using `ActiveSheet.HPageBreaks.Add Before:=Rows(nextRow)`.

5
Run and Test Your Macro

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.

Write a VBA Macro to Format Print Layout and Insert Page Breaks
Handling Blank Cells: To prevent the macro from creating blank printed pages, ensure your loop includes an 'If' statement to skip empty cells, or stops entirely when it reaches an empty title cell using 'If IsEmpty(ActiveCell) Then Exit Do'.
Advanced Macro Support in WPS Office

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. 1. Download and Install WPS Office: Visit the official WPS website, download the free suite, and install it on your device.
  2. 2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the print-formatting VBA macro.
  3. 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. 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.
Fully compatible with Microsoft Excel VBA code and macro formats (.xlsm, .xlsb).Built-in Visual Basic Editor for writing and debugging macros without extra plugins.Reliable print layout controls ensuring A4 settings translate perfectly to paper.Free to download with a lightweight installation and intuitive user interface.
microsoft office alternative - wps office

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.